Database Corruption and IO Errors

A quick way to have your day turned upside down and rip your gut out with nerves and anxiety is to come in one day to find that users are panicked, applications are not working and the HelpDesk team is curled up in the fetal position in the corner. Why? The sky is falling and everybody thinks the database has blown up.

Calmly, you settle in and check the server and eventually find your way to the error logs to see the following:

Msg 823, Level 24, State 2, Line 1

The operating system returned error 1(Incorrect function.) to SQL Server during a read at offset 0x0000104c05e000 in file ‘E:\Database\myproddb.mdf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Suddenly you understand and feel the collective fear and paranoia. What do you do now that the world has seemingly come to an end for your database?

Corruption

What exactly does this error message mean? Well, typically, an 823 error is a very strong indicator that there is some sort of problem with the storage system, hardware or driver that is in the path of the I/O request.

Great! That fear is getting a little heavier knowing what the error represents. This doesn’t bode well for the database. Let’s go ahead and crack out the list of what we can do or check when a problem like this hits:

  1. Check msdb.dbo.suspect_pages
  2. Run a consistency check for all databases on the same volume
  3. Check Logs (SQL, Windows, Storage system) to see if there may be additional info (via different errors/warnings) in close proximity to the 823 error.
  4. Check your drivers
  5. Restore the database

This is where your experience, training, and preparedness come in handy. An experienced data professional will be prepared with database backups (including log backups). So you are not concerned here because all of your backups are reporting successful. As you prep to pull the backups (for the past couple of days just in case) you notice that there are no available backups in your repository. Looking closer at your backup jobs you discover that the backups completed in mere seconds where they normally take hours for this database.

Now that your heart is racing, forehead is beading up with sweat, gut is sinking and the fear is very palpable – what do you do? Time to step through the rest of the steps and pull out your lucky charms, right?

Querying against suspect_pages, you find the table to be completely empty. You know that checkdb runs regularly but maybe it didn’t run last night. That is easy enough to check with a little query from here. Since a consistency check does not seem to have run (as confirmed by the script) and is the second item on the checklist, let’s go ahead and run it now.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Crud. Blood pressure and nerves are getting a little more frazzled now. Maybe we can cycle through the database and find which table is causing the problem. Let’s try a checktable of every table in the database. Before doing the checktable, one more check against suspect_pages still shows no rows to be found.

Running the checktable, every table is coming up clean except one. That one table produces the same sort of error as the checkdb and just so happens to be the largest and most critical table to the database. Blood pressure is closing in on critical now. We have a corruption issue that is severe enough that checktable cannot complete, we know we have 823 errors and some sort of IO issue and do not have a backup.

Wait…backup. Let’s try to force a backup and see what happens. We can tell the backup to continue after error so let’s see what happens. Maybe that will allow you to move the database to a different server or different spindles to try and just recover the data.

Msg 3202, Level 16, State 2, Line 1

Write on “E:\SQLBackups\myproddb.bak” failed: 1(Incorrect function.)

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

The situation just does not want to get any better at this point. Time for drastic measures – shut down the SQL Server services and try to xcopy the data and log files to a different server and try to re-attach from there. Anything is worth a shot, right?

Error 1: Incorrect Function

Glad to know it is not just SQL Server throwing the errors – sorta. The corruption is ever present and there is nothing more that can be done, right? All hope is lost. Time to fill out the resume and move on to something else, right? Give it one more shot. A spark of insanity hits and you wonder if a mere query against the clustered index will work, if not then maybe something against any of the indexes to try and spare any data at all.

You rub your luck horseshoe and query the table (yes the table that checktable aborts because of corruption). Lo and behold you get results from this very simple query. How is that possible. On a whim, you drop all the Non-Clustered Indexes and try a fresh backup.

Hallelujah! The backup completes without error. Time to take this backup and restore the database to a completely different server. Then on the new server run a consistency check to determine if it is all clear. To your liking, there is absolutely no corruption at this point so the non-clustered indexes can be recreated (easy to do because you have a script with the index definitions handy).

Wrap

This journey from fire and brimstone and the world ending to the epiphany and then the sweet euphoric feelings of success is not a normal resolution for these types of errors. This sort of thing happened for a client that called trying to get around the 823 errors. We had absolutely no indication whatsoever of where the corruption was beyond knowing we had failing disks at the time. We got lucky in that the non-clustered indexes in this case ended up being stored on the bad sectors and dropping those indexes allowed us to recover the database and make it usable.

When everything is breaking, the unconventional thought (especially without having a checkdb complete to tell you which index or which pages are corrupt) of dropping indexes may just save your bacon. It saved my clients bacon!

As a follow-up item, it is important to regularly check the dbccLastKnownGood for each database. That can be done by following the script in this article.

Message in a Bottle of XE

Comments: 1 Comment
Published on: December 27, 2017

One of the age old features that most probably take for granted in SQL Server happens to be the error messages. What? How can a message be a feature, right?

Well, I concede. Maybe calling it a feature may be too much. Then again, consider it for just a touch longer. Traditionally, SQL Server stores all of the messages pertinent to SQL Server and you can see all of them view the sys.messages catalog view. In addition to that, you can also add custom messages for your specific liking and environment. That last piece is a somewhat important concept in regards to calling “messages” a feature.

So, considering this “feature” for everything that is SQL Server related, does this also mean that all Extended Events related messages are accessible in sys.messages too? Afterall, Extended Events is a SQL Server feature too, right? And, we do occasionally see errors and warning messages in relation to Extended Events.

Messages

Let’s go ahead and try to test the theory that XE related messages are accessible to view inside of SQL Server. The first step will be to try and find those messages in the traditional location – sys.messages.

Let’s start with a sample session that will throw an error due to an invalid disk path (I don’t have a Z drive).

If I try to execute that script, I will receive the following message.

Msg 25641, Level 16, State 0, Line 8
For target, “package0.event_file”, the parameter “filename” passed is invalid. Target parameter at index 0 is invalid

Let’s take note of the message number – 25641. I will use that for exploration in the next step. With the message text and ID in hand, I can try and query the sys.messages catalog view to try and determine if this XE related message can be found there.

Running the preceding query will yield the following result.

Obviously that message text is different than what we saw in SSMS when trying to create the invalid event session. The difference in the text can be partly explained away due to the parameters. That said, there is an extra part of the error message that is not showing in sys.messages. Is this the correct message? Did XE do something to maybe append an additional message to this one from sys.messages? Let’s find out.

XE Messages

If I run this next query, I discover something that may be a bit unexpected.

I have discussed most of the other types in my 60 day series, but the message type has been neglected by me so far. That is a neglect I am looking to rectify right here. Let’s see what this message type might actually hold for us. Let’s get a little fancier with this next query to try and discover what we have in the message “type” in XE.

And a sample output from that:

As luck would have it, there are 82 messages for XE that are registered as “objects” within XE in SQL Server 2014 (and 84 in SQL Server 2017). That said, none of them have a message id tied to the message within the XE views or DMVs. So, if I need to try and correlate messages between the two sources, it becomes a little hairier. Now seems like a good time to try this hairy approach.

Scary Hair

Ok, so the query is not actually all that scary. That said, it is not likely to yield very many results due to the issues I mentioned already – the messages don’t entirely match up. So, what can I do instead to try and find these messages? Well, let’s change things up a bit and work off the presumption that the XE engine has added a message to the message 25641 and what we have is two different messages instead of just one. We already have the message for 25641. If we take the difference in that message with what was written to the screen in SSMS, we can do a bit of a search for that term.

Let’s try the following now.

This query is actually two queries. There is one to check the sys.messages view as well as a query to check the messages available via Extended Events. Instead of checking for the full text of the message, I changed it to just a few key words. As it turns out, there are few results that match real well the messages I received. Here are my results.

There we have both of the messages that comprise that single error message received when trying to create that event session with an incorrect file path.

Wrapping up

There we have it, Extended Events will throw a message that does contain messages from sys.messages as well as some additional custom messages in the XE metadata. Unfortunately, there is no way that I have been able to find to create custom XE messages. Quite frankly, I really don’t see a very good reason to create a custom XE message either.

There is a world of depth and knowledge within Extended Events. There is plenty to learn and plenty of ability to monitor and investigate the SQL Servers under our purview. I recommend you take a moment to dive deeper into this great feature of Extended Events.

Extended Events Permissions

Comments: 1 Comment
Published on: December 25, 2017

A common question that I am asked about Extended Events relates to what kind of permissions are required in order to use this awesome feature. In this article, I am going to compare and contrast the permissions required for Profiler as well as Extended Events.

Extended Events changed a lot with how to monitor your server. One of those changes is a slightly different set of permissions to be able to perform various different tasks. Before I dive into what the security landscape is with XEvents, let’s take a peek at what we have to do in order to be able to use Profiler.

Profiler

If you try to launch a profiler session as a standard login with what should be a typical user type account with restricted access, you will encounter something like the following.

Yes, according to the message you need to be a sysadmin (nobody reads past that first part so they always miss the alternative). So, let’s test things out. I will create a login called TraceUser and attempt to do tasks related to running a Profiler or Server Side type of trace. I will also use this same login when testing the permissions for Extended Events.

I included a quick test script after creating the login to confirm that the user does indeed have restricted access. The test script will produce the following message.

Msg 8189, Level 14, State 6, Line 8
You do not have permission to run ‘SYS.TRACES’.

This is the same net result as if I were to attempt to run a Profiler session – it’s just faster to get to the result. As we can see, the TraceUser login does not have the requisite permission to do anything with traces (profiler or server side).

Now, I will take this just a step further and try to add permissions to validate the minimum permissions needed.

Running this test script now will yield a result of all traces that are currently setup on the server. So, in my case that would be just a result showing the Default Trace and nothing else since I have not put any other traces on the server. I can also confirm the exact permissions granted to TraceUser do not actually exceed what I just granted.

Or is it?

After running that in the master database I will see that granting the “Alter Trace” permission has an extra side effect of granting “Showplan” to the user.

XEvents

For Extended Events, we have something a little different and a little more (just a little) granular.

Wow! That is quite the permissions escalation, right? Well, it’s not significantly different in some regards to what we see with Profiler. Taking it a step further, XEvents is more in tune with a lot more internals for SQL Server than Profiler. Little things like looking at DMVs or getting the current status for things like Hekaton or Availability Groups, which tie deeper into the engine, will require more permissions. That said, view server state or even these permissions levels is not the end of the world. There are work arounds to the permissions issues. Before I give the work arounds, lets look at the permissions.

Using the TraceUser login I created earlier in this article, I am going to start testing XEvent permissions.

The first step I took in this script was to revoke the “Alter Trace” permission. Then I queried both sys.traces and sys.dm_xe_objects along with running my permissions checks. The attempt to query the two views produces the following messages.

Msg 8189, Level 14, State 6, Line 34
You do not have permission to run ‘SYS.TRACES’.
Msg 300, Level 14, State 1, Line 36
VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
Msg 297, Level 16, State 1, Line 36
The user does not have permission to perform this action.

From the permissions checks, I can also confirm that this login has barely the ability to connect to SQL Server.

I will now grant permissions little by little to see what happens.

Here is the sum of the differences.

The “Alter ..Event Session” permissions should be expected since that is what I explicitly added. That said, the permission, while enough to create an event session does not yield enough permissions to do anything with viewing the session data or looking into the dmvs or catalog views. In fact, if you have a recent enough version of SSMS, the XE Profiler feature will cause SSMS to crash if you try to “launch” a session via the Profiler GUI with only the “Alter Any Event” permission.

That said, I can run the following script to create a new Event Session.

As we can see, the permissions are adequate to create a session via TSQL and it does not cause SSMS to crash. This would be another consideration if using the XE Profiler (and even if you are going to allow non DBAs to create XE Traces) in addition to what I noted recently in this article – here.  Truth be told, the traditional GUI for XE also will not allow users to access the Extended Events node if they have “Alter Any Event” or lower permissions – you must use TSQL to alter sessions. The caveat being that only the XE Profiler feature causes SSMS to crash (I tried it 6 times in a row with consistent results).

So, if “Alter Any Event” is only going to get us partially there, let’s go ahead and grant the view server state permission.

Due to the nature of sys.traces, I still do not have permissions to view that data. So that is a good thing. On the other hand, I can now query all of my XE related views (you can read more about those in my 60 day series). And I now can see that I have the following permissions.

And now I can do all sorts of fun stuff from the XE GUI.

That basically lays out the required permissions for both Profiler and Extended Events. In either case, I view both of these as tools for the trusted DBA. Outside of the DBA team, there are select other individuals who may think they need the access and then there are others who really could use the access to the tools but should not really be granted full sysadmin access. At times, you may not even want those individuals to have anywhere near “view server state” access because there is a chance that individual could see something they are not permitted to see. What do we do with that?

Work Arounds

This is when we figure out a way to circumvent the restrictions. Don’t view this as a hack to the system or a way to violate trust. Rather these are methods to help think outside the box and work together as a cohesive team.

Method 1 is a solid solution. That said, it does have a high management overhead. Aaron Bertrand has offered a solution and it is a secure solution. Aaron has found a way to work with Trustworthy databases, views, and logins to help provide a workable solution while still securing the event session data. You can read his article here.

Method 2 is also a secure solution and does require a bit more communication but less management and upkeep than method 1. I like to save the event data to file. Working with the person that would like to be able to review the data, I can create an appropriate XE Session that will be highly unlikely to capture anything they should not see. In addition, I do not need to grant that person any elevated permission on the production server. Once the trace is complete, I can copy the event files to a dev server where that person has elevated access. Then they can merge the XEL files (I show how to do that in the 60 day series) and review the data at their leisure.

Method 3 is similar to the previous method but it requires more work similarly to method 1. This last method, I can grant the user specific access to read data from a specific table in a specific database. I then setup an automated process to dump the data into that table or I can manually load it for the user. Again, the permissions here are very limited in scope and the person has nothing more than the ability to Select from a pre-defined table in a pre-defined database.

I don’t see the permissions for Extended Events as a limitation, rather they seem appropriate based on what XE does and the extensive nature of the tool. I recommend you work with XE for a bit and see what you can come up with to help improve your efficiency and the efficiency of our team.

 

 

Capture Index Maintenance Operations

Comments: 1 Comment
Published on: December 20, 2017

One good database maintenance practice is to keep the indexes in good working order. This is typically done via index defragmentation routines. Some people perform routine index maintenance, some people do not, and still others have done some sort of “set it and forget it” script provided by a vendor and may or may not be doing index maintenance while thinking they are.

When done properly, this is a good thing. Sadly, there are many cases where good practice is not adhered to (let alone best practice). Some of these less than ideal situations could be when an application vendor has something built into their code to perform some sort of index maintenance, unbeknownst to you. In my opinion this is a near-worst case scenario. Being diligent, a good DBA would try to establish routine maintenance for the database based on best practices (or at least good practices). Should that DBA do such a thing and there is index maintenance baked into compiled code somewhere that is not accessible to the DBA, you just doubled the work.

Corruption

Why is this a near-worst case scenario beside the point just made? Imagine being stuck, due to vendor requirements, on one of the versions of SQL Server affected by the online index rebuild bug (read more about it here by Aaron Bertrand), and for some reason you end up with a corrupted index every night. In fact, it is the same index that becomes corrupt on a nightly basis. Every day you drop and recreate the index, and lucky for you, it is not a unique index or a clustered index. But despite those efforts, the corruption continues to return. And since you know you are performing your index maintenance with maxdop of 1 or you have decided to do the rebuilds offline, you know for certain that your process is not the cause.

Take it a step further and you disable your maintenance routine entirely just to be 100% certain your process is not causing it. Yet when you check the recent update date for the index you find that it was still somehow updated/rebuilt while your maintenance was disabled. This is indeed a near-worst case scenario. This means that you have some digging to do to find when and how the index maintenance is being run and who/what is doing it.

How in the world are you going to find what/when this is happening without causing too much increased load on the server? Lightbulb!

Being on SQL Server 2012 or later, it suddenly occurs to you that Extended Events is an awesome tool and could very well be of some use in solving this problem. Let’s pull out that handy dandy script that we have used time and again to try and find potential Extended Events that could help us with this problem.

Running that code produces two very interesting results that seem plausible in this exercise. Those events are index_build_extents_allocation and progress_report_online_index_operation. Even the descriptions seem exceedingly promising.

Especially promising is the event named progress_report_online_index_operation. Look at it, the name tells us that it is a report on online index operations. Querying the available fields seems viable, the description is good, so give it a go.

Well, I will save you the trouble. I tried to create an index with the online = on syntax, tried a rebuild with online option, tried a reorganize option, and tried every way I could think to rebuild an index online. I just wanted to get some sort of data and I could not get it to fire while using the asynchronous_file_target (I really wanted to use the file target for ease of investigation later). Though the event_file target doesn’t appear to work, I was able to get results by sending data to the ring_buffer or to the Livestream target.

What does that mean? If you decide to watch the live data from SSMS, then you invoke the Livestream target, and you can see the data as it streams. Of course that also means you have to sit and watch to try and find when this phantom online rebuild occurs. If you choose to use the ring_buffer, then you run the risk of events cycling out of the buffer. This may or may not be a bad thing since you basically need just an event showing the source of the rebuild and the statement used.

XEvents for the Save

Without further ado, let’s see what we can do to find these online index build operations so we can hopefully target the additional maintenance that is causing us grief.

Notice that in this session I have decided to target a very specific database. For me, this database happens to be my ReportServer database. And for testing purposes, this works wonderfully. All I need to do is something like the following rebuild operation.

This is a rather innocuous index for me. In addition to it being on my test instance, it is in a database that is used lightly and a table that is used even more lightly. If you are looking to test, I recommend you find a similar scenario and definitely test somewhere other than on a production box.

Having rebuilt that index online, I can then query the Event session with TSQL using a query similar to the following:

Now I have an abundance of data at my fingertips to determine how this index continues to get rebuilt. And since this is likely happening in the middle of the night, and that I have to rely on the ring_buffer, I might decide to set an agent job that will run this query every hour or two, to see if I can trap the necessary data about how my indexes are getting rebuilt without my knowledge.

This is just one more tool that can be thrown into your utility belt or tool shed to help find those little things that cause us pain from time to time. I hope it helps you, whether in the same scenario I just described or otherwise. This could even be of help when trying to determine timings of defrag operations when trying to plan for maintenance windows.

Linked Servers and Stats

Linked Servers

A linked server is a fabulous feature in SQL Server to help accomplish various data tasks between local and remote servers. There is a time and a place for the use of linked servers. Unfortunately, some are of the belief that a linked server is the solution to all problems including the way to end world hunger.

You may recall that I have written about linked servers on a few occasions. Usually that is due to the relationship of a poorly performing process and the linked server. Here is a prime example here or here. On the other hand, here are some examples of using a linked server in a much better light here and here.

Using a linked server is entirely up to you. If you choose to use a linked server, you will need to understand there are risks involved – beyond just the documentation and performance nightmares it can cause. Today, I will share one of those fabulous gotchas – mostly because it has cropped up more than once with my clients.

Remote Stats

On more than one occasion I have had an emergency request because everything was broken. The everything in almost every incident is an SSIS package that is failing with error messages. The error message will typically have text similar to the following:

Could not locate statistics ‘_WA_Sys_00000015_346C780E’ in the system catalogs.

Due to the error, the package fails processing and grinds to a halt. When diving into the package it is discovered that the missing stats happen to be coming from a linked server query. This raises a big bright blaring alarm for me. Why is the SSIS package accessing the data via a linked server? This is rather counter-productive and definitely contrary to what is desired from a performance perspective.

You can certainly see why this is a sudden emergency right? Absolutely nothing is working anymore, right? Well, at least that is the way it seems for the person reporting the issue. Understanding that their realm of work has come to a halt is important in order to get to a resolution more quickly. Knowing that the point of failure is not as grande scale as claimed is critical because it can help you keep your cool while assessing the real problem.

Since the problem presents itself when accessing a specific object or even set of objects across a linked server, we have our scope of the issue scaled down quite a bit already. Now, what do we do from here? Obviously the statistic with that name is missing, right? Don’t be so hasty to assume it is actually missing. Let’s verify that the stat is indeed missing first. Even better – let’s just eliminate as many variables as possible. We can do this by querying the affected objects directly from SSMS.

Easy Troubleshooting for the DBA

For me, it is essential to eliminate variables when we run into a failure. In this case, I have just a few major variables that can potentially be causing this problem. Those variables are:

  1. SSIS
  2. Linked Server
  3. The query itself
  4. Security
  5. Bad Plan
  6. The stat is legitimately missing

I can easily assess the legitimacy of each variable through various quick tests. To eliminate or validate the “query” variable, I can issue a very simple query to retrieve data from the affected object. So let’s eliminate variables 1 and 3 in one fell swoop.

This query, in this scenario, results in the following:

This, while disappointing, is actually quite productive. This has eliminated two variables for me. I now know that SSIS is not related to the problem. I also know that query complexity is not related to the problem. There are still a few variables left to either validate or eliminate. Since I know the problem occurs when querying via linked server, let’s try querying the remote server direct (not via the linked server).

Well, while that does not entirely eliminate or validate any variables, it does tell me that the problem is still specifically related to the linked server. What if I try to use OPENQUERY instead of the traditional linked server query?

Wow, look at that? Ok, so this is a bit of trickery because I have told SQL Server to execute that query on the linked server as if it were a “local” query on that server. This does work without error and is definitely pushing the findings to be more conclusive that it is a linked server problem.

While the openquery version works, I do still need to eliminate some variables from the problem. One of the variables is security. Since the error mentions sp_table_statistics2_rowset, I googled about for that proc and found some mentions that maybe there are some column denies related to the stats in question that is giving the linked server some major fits. Or it could also be insufficient permissions to execute DBCC SHOW_Statistics. I highly doubt this to be an issue since the openquery version works while using the same pass through authentication of the linked server that the traditional linked server query would use.

In order to eliminate security as a potential cause, the test is simple (while it could be more complex, I went for the jugular to just eliminate the theory as quickly as possible) – I will add my account as the pass through account (which is a sysadmin on the remote server) and then query the linked server all over again. Suffice it to say, there was no change in the result – the error persisted.

This does not just yet eliminate the security variable because there could be a cached plan somewhere. So, just to be sure, I chose to flush the cache on both the linked server and the local server. Running the simple “Select *” query all over again yielded no difference in the query results. I can now safely say that the problem is not related to a cached plan nor is it related to the security. At this point, I set the linked server security back to the way it was. I have effectively eliminated all variables but 2 and 6 (linked server and the stat is missing).

Let’s eliminate the missing stat variable right now.

Undoubtedly you have noticed that I built a drop statement into the result set from this particular query. That aside, the green highlighted row is the very stat that was producing the error. This stat is most certainly available on the source server (linked server). This entirely eliminates the sixth variable because the stat is not missing.

This brings us to the final step – see what happens when we drop the stat and try the query again. I have the drop statement ready to roll, so let’s just run that and see what happens. My risk here is very small. This is an auto-generated stat and will be recreated if needed. After dropping the stat, I run the simple “Select *” query across the linked server again and guess what? It works perfectly fine.

In my opinion, we can just skip a few of these steps if the problem is on a system generated stat and just go straight to dropping the stat and trying again.

That said, the better solution in this case would be to do one of two things.

  1. Modify the Package to not use the linked servers in this way and create a data source connection for each of the different server connections.
  2. Modify the Package to use OPENQUERY style queries.

My preference between those two options would be to create specific data sources. It seems very silly to pull data across the linked server to then be consumed via SSIS package.

I have just illustrated what I would classify as a nuisance problem related to linked servers. Simplifying the approach as I described with the two potential solutions would alleviate this nuisance while also giving an inevitable performance boost.

Parting thought: One observation of the “fix” that involves dropping the stats is that on busy systems it is a very short lived kluge. I have seen the problem re-surface within five minutes on busy systems. The stats are not really a problem, it is just how the linked server connection interfaces with the stats.

What’s the Size of that Index?

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
Published on: July 27, 2015

I have previously written about finding what the big space consumers are within the database. I even shared scripts in those articles on how to find those big consumers (read all about it here).

sizematters2That is grand when it comes to quickly finding which objects happen to be consuming the greatest amount of space within the database. It really helps to quickly assess where some extra TLC may need to be spent. But what about more specific details? What if you wished to figure out what the size was for each of the indexes within that object?

Well, the solution is pretty simple. I have another script to help determine which indexes are the big space consumers within the specified table for the given database.

 

First, let’s see what that script is.

As you can see, I am parsing a few different measures on space usage from the dynamic management view sys.dm_db_partition_stats. This will tell me information such as how much space is being reserved for each index as well as how much space is actually being used on each index. Then I use a grouping set to summarize that size information so I can then compare the total index size back to the size I would have seen from the table space script (discussed lightly earlier).

Why might I want to do something like this?

That is a really good question! The first obvious answer to a question such as this is in the off chance that you discover (again, from the use of the previous script), that one of your terabyte tables is actually only 150GB of data and the rest of that space consumption is actually from all of the indexes on that table.

When I see that a table has more space allocated due to indexes than due to data, I might be curious which indexes are contributing to that consumption. In addition, I can look at numbers much faster to see if maybe there are multiple indexes that are the exact same size. If they are, I might want to evaluate and dig a little deeper into the definitions of those indexes. We would only really love to have 850GB of indexes on a table to only find that three or four of those indexes are exact duplicates.

From there, I might even decide to look at the usage stats for the indexes in question. Why go about it in this fashion? Well, on some databases it is much faster to run a few quick queries than to try and run a big query against the entire database to get all of the index usage stats. I am sure you are familiar with those times when trying to query physical and operational stats takes hours to complete.

But But But…

I can hear you thinking right now. Another question has popped into your head. What about sp_spaceused. That old proc does work just fine for this as well. But there is something about being able to get to know what contributes to the space of an index and being able to work through how to get it calculated. Additionally, with a minor tweak, this query can provide that (detailed) information very quickly for all objects in the database.

Tweaked query

 

Missing Indexes Script v2

Comments: 11 Comments
Published on: May 15, 2014

missing_idx

Frequently we hear the analogy that <insert item here> is like opinions, everybody has one and not all of them are good (some may stink).

Well, this may just be another one of those <items>.  Whether it stinks or not may depend on your mileage.

I had shared a similar script back in January 2012 and wanted to share something a little more current.  As is the case for many DB professionals, I am always tweaking (not twerking) and refining the script to try and make it more robust and a little more accurate.

This version does a couple of things differently than the previous version.  For one, this is a single database at a time (the prior version looped through all of the databases with a less refined query).  Another significant difference is that this query is designed to try and pull information from multiple places about the missing indexes and execution statistics.  I felt this could prove more advantageous and useful than to just pull the information from one place.

Here is the current working script.

The following script gets altered on display.  n.VALUE is displayed but in the code it is actually n.value.  The code display is wrong but it is correct in the code as presented in the editor.  If copying from this page, please change the U-cased “VALUE” in the XML segment to “value” so it will work.  A download of the script has been added at the end.

[codesyntax lang=”tsql”]

[/codesyntax]

As has been the case in the past, this is not the end query.  I have a backlog of updates I want to make to the query and try to create something even more useful from it.

As a bit of fair caution, just because an index shows up as a recommended implementation from this script, it does not mean you should blanket implement it.  Also, like the previous script, the higher the impact value, the more probable the index will have a positive impact on your system.

In addition to the impact, I also look at the “Similar Queries” and “Similar Plans” numbers from the result set.  These values can sometimes lead to an understanding that their are other queries or other plans that could also benefit from the creation of the index.  Your mileage may vary.

Download script Missing_Index.

Quick and Dirty Index Info

Categories: News, Professional, Scripts, SSC
Tags: ,
Comments: No Comments
Published on: November 1, 2011

From time to time, somebody needs to find some information about the indexes in the database.  As DBA’s, there are a few tools at your disposal to look up that information.  The required/requested information is not always the same.  That said, you can usually adapt a query quickly to find what you need.

Where to Look

SQL 2005 and up have provided us with some very useful means to gain insight into the indexes in our databases.  The following DMOs is a small representation of that information.

  • sys.dm_db_missing_index_details
  • sys.dm_db_missing_index_group_stats
  • sys.dm_db_missing_index_groups
  • sys.dm_db_index_usage_stats
  • sys.dm_db_index_physical_stats

Add to that, there are some catalog views to help with finding information relevant to indexes in the databases.

  • sys.indexes
  • sys.index_columns

With just these few objects, there is a wealth of information at your fingertips.  I often find myself querying for information about indexes.  I also find myself being asked how to find this information on a relatively frequent interval.  Usually, I just end up writing out the query again (depends on mood and day).

Rewriting the query doesn’t take too much time when you know what you need to query.  That said, sometimes it is nice to have a base query ready to go.  From this query you can add/remove items as you see fit to get the information that you really need.

For instance, here is a query to get some of the fragmentation information.  I would probably just re-use this query over and over where applicable – only changing the parts that mattered to get the necessary result set.

[codesyntax lang=”tsql”]

[/codesyntax]

There are many types of queries we could write for the different relevant information pertaining to our indexes.  It is all a matter of knowing where to start.  I hope that knowing about these DMOs and catalog views, it will help shorten your efforts to retrieving this information.

Table Sizes

Comments: 2 Comments
Published on: May 3, 2010

In SQL Server there are two stored procedures that help one to determine the size of a table.  One is documented and more widely popular, the other is not.  These two stored procedures are sp_spaceused and sp_MStablespace.  Dukagjin Maloku wrote a blog article about the the two stored procedures in March of this year.  I don’t intend to rehash what he has covered.  What I intend is to look at the internals of the two stored procedures.  This will be the first of a multi-part series.

Why?

I saw a question asking how to find the table sizes for all of the tables in a database.  A few scripts were provided (one using sp_MStablespace and one using sp_spaceused).  I had been using the msTablespace version for quite some time and had never questioned it’s accuracy.  That changed when the question evolved into why do they (the two scripts) show the same results?  So, I decided I needed to figure out what is happening inside both procedures to see if I could determine a reason for the difference.  I will be comparing these on SQL Server 2008.

Comparison

sp_spaceused

This stored procedure has been mostly updated to use the new DMVs available since SQL 2005.  It currently relies heavily on sys.dm_db_partition_stats.  This procedure determines the size of the indexes by subtracting the number of pages from the number of used pages.  The idea here is that the number of Data Pages will be less than the total number of used pages.  This stored procedure does still rely on a soon to be deprecated object called sysfiles.  The replacement for that object would be sys.database_files.  The functionality would be somewhat different, in regards to how it is used in this procedure – the results are the same.  Despite that, this proc does not look to be on the replacement block any time soon.

sp_MStablespace

This stored procedure on the contrary does look like it will be deprecated, though I was unable to find information to back that up.  When delving into the procedure, one can quickly spot that it relies on sysindexes and sysobjects for much of its heavy lifting.  The sizing information will be coming from sys.partitions, sys.allocation_units and sysindexes.  This procedure uses sysindexes mostly for that sizing information and then calculates the data size based on clustered indexes and heaps.  Then to calculate the remaining index size, it subtracts that size from the total for all of the indexes on the specified table.  The math is a bit different and can be the reason for the difference.  This procedure seems to be in need of a decent sized overhaul if expected to not be deprecated.

Findings

After looking at the code and then running some tests with each of these procedures, I was able to corroborate the claim that they don’t return the same results.  With that in hand, I decided to take a stab at writing something different to help create a consistent return.  Thus I will be sharing some queries I have been working on for each of these.  The desired goal is that both would return the same results.  Based on the consistency of information between the DMVs and the necessary (and some bonus) elements for these queries, that may be a bit more difficult to do.  For the sp_MSTablespace query, the crux of the query will be the sys.dm_db_index_physical_stats function, while the sp_spaceused query may continue to rely on sys.dm_db_partition_stats or just sys.partitions.  Both queries may rely on a combination of these latter two DMVs, tune in to find out.

BLOB Index Columns

Tags: , , ,
Comments: 1 Comment
Published on: April 27, 2010

For the April T-SQL Tuesday, I blogged about a report to find information on the BLOBs in a database.  I have since seen a request to add to that script.  The addition would add some good information concerning the columns involved in a BLOB index.  This information is to find all of the columns that are involved in the index that includes a BLOB in the index.

Base Script

In that article I posted a script to help arrive at the final report.  There were a couple of things required for the setup.  I am including all of that information here in a single script.

[codesyntax lang=”tsql”]

[/codesyntax]

In this script, I made a slight alteration from the article I posted.  In that article, I somehow missed a change to the script I had been testing.  That change is in the Temp table that I created (to properly support the Join statements on each side of the Union Select statement).  I simply added the object_id.

The Change

[codesyntax lang=”tsql”]

[/codesyntax]

The guts of the change to add in the columns for this script comes with the following segment of code.

[codesyntax lang=”tsql”]

[/codesyntax]

With that snippet, I also needed to Join it to the select statements, and thus it was added on both sides of the Union statement as illustrated in the next example.

[codesyntax lang=”tsql”]

[/codesyntax]

In the above CTE, you will note that I used the STUFF function along with a FOR XML Path statement.  The combination of these statements allows one to concatenate a list into a comma separated list as I have done with the ColList (column list) column.  I also want to note here that I am using the text() keyword along with the FOR XML Path.  There are other methods of returning information back to the screen when using FOR XML Path.  I chose to use the text() in this case because I am just returning a concatenated list of columns that really only should read as text.  If I were returning a SQL statement, I would choose a different method to make the text more readable.

Conclusion

I chose to make this subtle change via the CTE due to the ease of understanding and readability of the code for me.  By illustrating the columns involved in an index that is on a BLOB column, one can gain greater insight into the use of the database.  I am glad that this change was requested because it makes sense to me.

I hope you find it useful.

Edit: Made a correction to the first script

«page 1 of 2

Calendar
November 2018
M T W T F S S
« Jul    
 1234
567891011
12131415161718
19202122232425
2627282930  

Welcome , today is Saturday, November 17, 2018