Archive for July, 2011

SQLDIY: Index Usage Statistics

There are several different aspects of indexes that can be monitored via DMV’s. One the neatest things is just how much your indexes are used, how they are used or if they are used at all! I’ve put together this stored procedure to gather a ton of useful information on just how your indexes are being used. Using this information you can determine if the index in question is really needed, or if it is truly critical and shouldn’t be messed with. As will all DMV’s if your server was just rebooted you may see quite a number of unused indexes. By sampling data over time we can see when an index More >

SQLDIY: Index Fragmentation Levels

Indexes, indexes, indexes my kingdom for an index! Well, something like that. Indexes are the single most important structure in our relation database world. So, it is understandable that I do everything I can to gather information, monitor their health and maintain them to the best of my ability. One of the keys to index health is how badly fragmented the index is. Many people simply reindex everything on a regular schedule. While this may be fine for smaller indexes say under 50 megabytes but not so great for indexes that may be very large 400 megabytes or more. Knowing how your database is More >

Pliant Technology, Enterprise Flash Drives For Your SQL Server: Part 2

Adding In Others For Contrast

In our first part we introduced Pliant and the LS 300 drive. In part 2 we get down to the details. To give a better idea where you stand with the setup described last time I’m throwing in two other storage setups. A RAID 10 array made up of 12 500GB 7200 RPM drives attached via SATA II controllers In a RAID 0 configuration I was able to get 800MB/sec in sequential throughput so it isn’t horrible, just not “enterprise” worthy. A Patriot Torqx 128GB based on Indilinx Bigfoot SSD controller, not the greatest SSD on the consumer market but Indilinx was the king of More >

SQLDIY: Statistics Information Including Last Update

One of the most important aspects of keeping your system running well is updating and managing your statistics. Since the optimizer relies on statistics to determine the best execution plan it is imperative that you keep an eye on them and maintain them just like you would indexes. One of the disappointing things in SQL Server 2005/2008 is the deprecation of some of the functionality of the sysindexes system view. In particular the rowmodctr column, since there is no equivalent in any of the new sys schema DMV’s. With that said, the GatherStatisticsInformation stored procedure listed here is More >

SQLDIY: Gathering A Data Dictionary

In this installment we are looking at assembling a basic data dictionary from the column level meta data stored in SQL Server. This is a little different from the rest. You can still do quite a bit with the information provided but to leverage this to the fullest requires a bit of developer buy in. Or a bit more work for whomever is your schema designer. Extended properties have been available to you since the stone ages of SQL Server. Two fine fellows Mark Chaffin and Brian Knight(blog|twitter) expound on the power of extended properties in the article on Managing Metadata in SQL Server More >

Go to Top