Denny Cherry shows us that you can make Management Studio beep when a batch completes:
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.
Jon Morisi has a script to find orphaned users:
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.
Kevin Eckart shows you users with recent password changes:
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.
Randolph West had to tune a query taking 10-100x too long:
A stored procedure with a single
@ProductIDparameter would allow us to addWHERE 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
JOINs andWHEREconditions, 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.
Erin Stellato troubleshoots a cumulative update installation problem:
SQL Server Setup failure: The directory name is invalid.
The initial email didn’t have a lot of details, so I started asking questions to understand what version was being installed, the environment configuration, etc. Turns out this was a two-node Windows Server Failover Cluster (WSFC) with multiple SQL Server 2012 instances installed, and one of the instances was still running on the node this person was trying to patch. To be clear, the two nodes were SRV1 and SRV2, and the instances were PROD-A and PROD-B running on SRV1, and PROD-C which was running on SRV2. This person was trying to install the cumulative update on SRV2.
The behavior Erin describes is a little bit crazy, but at least there’s a good explanation and way to solve the issue.
Erik Darling has a short checklist of some things to check before upgrading SQL Server 2005:
MIND YOUR COMPATIBILITY LEVEL
When going to 2014 (as of today, 2016’s RTM hasn’t been announced yet), you’ll have to decide whether or not the new cardinality estimator suits you. There’s not a cut and dry answer, you’ll have to test it on your workload. If you’d like some of the more modern SQL features added to your arsenal, you can bump yourself up to 2012-levels to get the majority of them.
The interesting survey would be, among people who still have SQL 2005 installations, how many will move as a result of Microsoft declaring end-of-life for 2005. My expectation is a fairly low percentage—by this point, I figure a at least a strong minority of 2005 instances are around for regulatory or compliance reasons (e.g., some piece of regulated software was certified only for 2005).
Andy Mallory continues his discussion of compression options:
You can think of page compression as doing data deduplication within a page. If there is some value repeated in multiple spots on a page, then page compression can store the repetitive value only once, and save some space.
Page compression is actually a process that combines three different compression algorithms into a bigger algorithm. Page compression applies these three algorithms in order:
1) Row compression
2) Prefix compression
3) Dictionary compression
Page compression is my go-to compression option, typically. There are some cases in which it doesn’t work well, so check beforehand (start with sp_estimate_data_compression_savings), but I’ve had good luck with page compression.
Jesse Seymour talks about junk dimensions in warehousing:
I think one of the single biggest challenges I face as I attempt to warehouse data that originates as a SharePoint list is the handful of miscellaneous descriptive fields, such as approval status, request status, or something similar. Typically, this fields are setup as Choice fields in the SharePoint list so they have a known range of values, but its still a pain to have to build a dimension for each one.
Enter the junk dimension. Ever since I learned about this concept it has made my life so much easier. What the junk dimension does is perform a cross join against the different fields and creates a row for every possible combination of fields.
Junk dimensions are nice for those low-cardinality attributes which are important but don’t really fit anywhere else. The important thing to remember about a junk dimension is that you don’t want it to be too large: if you have 5 attributes, each of which has 8 possible values, you have 8^5 (32,768) rows. That’s not so bad, but make it 10 attributes and now your table has 1,073,741,824 rows, and that’s a lot of rows for a single dimension. If you find yourself in that scenario, you might want to create two junk dimensions (bringing you back to 2 dimensions with 32K rows), review your design to see if all those attributes are necessary, or review your design to see if your “junk” dimension is hiding a real dimension.
Grant Fritchey gives an introduction to wait stats:
Now, you have a meaningful list of wait statistics that will tell you exactly why, if not where, your server is running slow. Unfortunately, these waits still need to be interpreted. If you read further on Paul’s blog, you’ll see he has a number of waits and their causes documented. That’s your best bet to start understanding what’s happening on your system (although, I hear, Paul might be creating a more complete database of wait stats. I’ll update this blog post should that become available).
Wait stats are fantastic tools for figuring out your server resource limitations given the server’s historic query workload. They’re the first place to look when experiencing server pains.
Kevin Feasel
2016-02-25
SQL Server Management Studio