Anyone who has used SSRS in the past has probably been slightly frustrated with the lack of control for parameter positioning. It was possible to have some control by manipulating the ordering of the parameters, but for 2016 we have a new interface to define the positioning. It’s basically a grid onto which parameters, along with their labels, can be placed.
The big one for me: HTML 5 support. I remember spending so many hours trying to figure out why reports wouldn’t display in Firefox correctly or why they sometimes wouldn’t work at all (because the report builder executable wasn’t installed correctly or that one time there was a bug in the executable)…and that was before mobile took off as a serious platform.
NC_TABLE1 is 36 total extents. 288 eight k pages. 2.25 mb. It can be read in 5 reads – one read for each contiguous run.CI_TABLE1 is comprised of 48 extents. 3 mb. It can be read in 11 reads – again, one for each contiguous run.The SQL Server instance has the -E startup option enabled. Without that startup option, proportional fill algorithm would distribute incoming data to the 8 data files with a potential smallest allocation of a single 64k extent before rotating round-robin to the additional files in the filegroup. With the -E startup option, the smallest possible new allocation by proportional fill is sixty four 64k extents – 4 mb.That means if I can rebuild the indexes into new space at the tail of the files, the contiguity should be improved considerably.
I had never considered that the scenario described here before, so this was definitely interesting.
Now, no matter what value I pass it, the RECOMPILE hint is effectively ignored. I have the same plan every time (in my case, the London plan). Ah, but is the RECOMPILE hint ignored? In fact, no, it isn’t. If I capture the extended event sql_statement_recompile, I can see every single time I execute this procedure that it’s going through a full recompile… which is then tossed and the plan I chose is forced upon the optimizer. Every time.
This tells me that if you were using OPTION(RECOMPILE) to get around nasty parameter sniffing problems before, and if you go to Query Store, and if you force a particular plan to get around said nasty parameter sniffing problems, then you probably want to update the query to get rid of that OPTION(RECOMPILE) in order to prevent unnecessary plan compilation work.
When multiple files are involved, and if these are ideally located on different physical spindles on the underlying disk subsystem, then a rather nicely performing data striping can be achieved for the database. If proportional fill kicks in and starts to focus on files with more free space then you may get hot spots for those files. However nowadays with auto-tiering SAN’s, SSD and (abstracted) cloud storage (for IaaS deployments) this is beginning to matter less and less.
This is a good introduction to proportional fill, including what happens when you add files later. If you are counting on proportional fill, it’s a good idea to make sure all files are the same size and grow them all at once.
As with most of these types of things, I had a need. I want to show how using sys.dm_exec_[requests/sessions/connections] is better than sp_who. Particularly when you have a large number of connections. Well in order to do that I need a large number of connections right? Now I’m sure someone out there has a script to generate somewhat random connections but writing one myself would be good practice and I’d like to get better at Powershell anyway. In the end I need some help and as aways it was plentiful and easy to find. So thanks to Derik Hammer (b/t), Drew Furgiuele(b/t), and of course it wouldn’t be a PoSH script if I didn’t get help from Mike Fal (b/t). (To be honest Mike actually wrote most of the final script)
This is great for demonstrations, and with a few tweaks you can turn this into a very poor man’s load tester.
The next time you run a query (you might need to close all your query windows or restart SSMS, you usually do with this sort of change in SSMS) it’ll beep when the query is done.
Personally I’ve actually used this as an alarm clock when doing long running overnight deployments so that I’d get woken up when the script was done so I could start the next step. It’s also handy when you want to leave the room / your desk while queries are running.
I must have seen that screen dozens of times and never once noticed this checkbox.
To solve the performance problem I went straight to the DefaultBufferMaxRows setting and set it to be equal of the maximum number of rows in a Row Group – 1048576. Together with the AutoAdjustBufferSize setting it helps the actual current size of the DataFlow Buffer that will be used for transferring the data from the source to the destination table.
What should I say – it worked like magic:
I guess that with 2:09 Minutes the clear winner of this test is the configuration with AutoAdjustBufferSize set to True and the DefaultBufferMaxRows to 1048576. It took less then a half of the time with just AutoAdjustBufferSize activated and the insertion process was executed with the help of the Bulk Load API – meaning that we did not have to wait for the Tuple Mover or to execute it manually.
Doubling insertion performance is nothing to scoff, especially for something like columnstore tables, where we expect millions (or more) of rows to be inserted.
With the SQL Server 2005 end date approaching, you may find yourself migrating databases. One of the gotchas with any database migration is orphaned users. Below is a script I put together, and have been using for years, to help me resolve issues with orphaned users:
I’m not a fan of the sp_msforeachdb in there because there are issues which can cause “each” database to skip databases. If you have installation authority, Aaron Bertrand’s sp_foreachdb is a better alternative, and if you need to do Insert/Exec calls, there’s a newer version which removes the INSERT/EXEC in that procedure.
There may come a time when you need to generate a list of SQL logins and the last time their password was changed. By using the LOGINPROPERTY of the name in sys.server_principals we can generate such a list.
Note: This will only work with SQL logins.
If you want (or need) to know that passwords are being updated but can’t turn on password policies, this at least answers the initial question.
A stored procedure with a single
@ProductIDparameter would allow us to add
WHERE ProductID = @ProductIDto both derived tables, which would make the query really fast. In my testing, it ran in under 100ms.
The problem is that this would require numerous code changes to the existing system. Many of the queries that rely on the view also use additional
WHEREconditions, meaning that a stored procedure is impractical.
Enter the table-valued user-defined function, or TVF.
I’m glad that the TVF worked out for him, but personally, I’m not a big fan of functions. In this case, though, it does the trick and leaves everyone happy.