A Fine Slice Of SQL Server
Michael J Swart has updated the Blocked Process Report Viewer:
Comments closedThe viewer can consume blocked process report events captured by any extended events session as long as that session has a target of
ring_bufferorevent_file. For example, if you set up your extended events session the way Jeremiah Peschka did in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. Then you would use the viewer like this:
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.
Comments closedRolf Tesmer shows how proportional fill for files in a filegroup works:
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.
Comments closedJon 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.
Comments closedKevin 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.
Comments closedErin 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.
Comments closedErik 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).
Comments closedAndy 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.
1 CommentGrant 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.
Comments closedJes Borland wants us to stop copying and pasting files between servers:
IT professionals (and amateurs), it’s time we had a chat. It’s time to stop dragging and dropping (or copying and pasting) files between servers and/or workstations.
It’s clumsy. It’s childish. It uses memory on the server.
Oh, and there’s a really easy tool to copy files built into Windows – Robocopy.
The syntax is pretty easy and robocopy handles small files well. Check out Nic Cain’s comment, though, if you’re going to copy large files in production.
Comments closed