Tested on Sql Server 2008 and 2008 R2.
It is simple, isn’t it?
In a long running project the database is changing day by day. We create new tables, optimize our queries, and of course, make new indexes. Usually when I finish an optimization process I forget an important step:
I always forget to check how many indexes I have that I don’t use anymore.
Sql server stores index statisctics in a sys.dm_ view named SYS.DM_DB_INDEX_USAGE_STATS. As the other sys.dm_ views, sql server clears statistics on every restart, so we have to check after missing indexes only for our database instance had run for significant time and we had collected enough statistics to decide if we delete an index or not.
Now after we have understood the basic, let’s see the query for showing our index statistics:
As we can see on our column names, we access seek, scan, lookup and index update statistics counted since the last start of our sql server instance. Our result can contain null statistics. In that case we don’t have any statistics about the given index since the last restart.
This script contains one parameter (@table_name). If we uncomment the 4th line, we can filter for indexes only on a given table.
Nice example that shows what happens if we don’t have enough time to read a book about ‘Teach Yourself SQL Server Programming in 24 hours’ and start programming without knowing the tool that we use.
After we check msdn, we can easily check if two dates are in the same day:
A simple solution how to query random N records from a database table without iteration on a full table:
In most cases loading large TreeView in one step can be a good performance killer. The generated html is really large and our page is loading so slow at the client and the average user don’t want to see the full tree together.
Let see, what needs for loading dynamically an asp.net server control on the server side.
We need a method that queries the list of the child nodes for a given node (DAO\CategoryDAO.cs):
Add a TreeView for the page (Default.aspx):
Make an AddChildNodes method for populate the child nodes of the given node (Default.aspx.cs):
Load the root elements on the first page load (Default.aspx.cs):
Create an event handler for the TreeNodePopulate event (Default.aspx.cs):
The full project is downloadable from my Skydrive public folder.
Happy and Bug free coding!