Shrinking Databases

SQL Server
Tim Ford put up a hilarious blog post outlining an instance where shrinking databases is acceptable. I'm pretty sure the development method he describes is well documented and in use in more than one location around the world, including somewhere in my company. Read it.
Read More

Three Kinds of Execution Plans

SQL Server, T-SQL
You read that correctly, three kinds of execution plans. You may have thought that all you had to deal with are estimated and actual, but there is one more. The estimated plan is the plan that comes out of the optimizer. It's based on statistics and indexes and known objects within the system. The actual plan is the plan that was used to execute the query and will show all the actual number of rows processed, etc. It might be different than the estimated plan because the stastics were off or for any number of other reasons. Those were the ones you knew about. There is also the plan that gets stored in the plan cache, the compiled plan. I lied. The compiled plan and the estimated plan are the…
Read More

Public Speaking

PASS
A great post on the how's, why's, etc., of presenting by Paul Randal. I found it because of Database Weekly. Some of the discussion are things I've read about in other places with more more or less detail. But please, don't think I'm knocking it in any way. It's a unique look at presenting from someone who does quite a lot of it. There were a number of topics and ideas that I hope I can apply going forward. I understand that PASS is going to open the gates for abstracts soon. I think I've got three ready this year, although one of them is a bit rocky. Still, armed with a more knowledge than I had, thanks to Paul Randal's help, I think I can do better.
Read More

Networking

Misc, PASS
Andy Warren has just posted the last entry in a very interesting set of posts about building and working your own network. I think they're all worth a read, especially if you've been thinking about blogging, Twitter, LinkedIn or just volunteering at your local users group.
Read More

Refining the Missing Index Data

SQL Server, T-SQL
In my previous post I showed how you could determine if a query had missing index information, but it was difficult to get meaningful data out because I didn't know XQuery well enough. I spent some more time refining the query and here are the results. This new query will show some basic performance information gathered from the aggregate data in sys.dm_exec_query_stats. It combines this with the full data pulled from the Missing Indexes element in the XML of the execution plan. I've got it listing all the recommended columns and grouping. So this means that the performance data is repeated in order to allow for the full listing of groups & columns. It seems to work well. A couple of interesting points. My purpose is to provide a short-cut…
Read More

Missing Index Information and Query Stats

SQL Server, T-SQL
So the goal was to find a way to pull information from sys.dm_exec_query_stats so that we could identify poor performing procedures that were in cache at the moment and combine it with missing index information from sys.dm_db_missing_index_details. We're combining these because we're working with a Microsoft Dynamics CRM database that is almost all ad hoc queries and lots of them are against tables with missing indexes. The hope was to identify necessary indexes merely by looking at the longest running queries. Unfortunately there is no way to combine data from the missing indexes set of DMV's and all the execution DMV's that show query stats, execution plan, etc. None of the missing index tables has a plan handle or a plan hash column that would allow you to combine that…
Read More

PASS Chapter Spotlight

Misc
The Southern New England SQL Server Users Group was picked for the February spotlight on the PASS web site. This was largely based on the work we did helping Adam Machanic run the New England Data Camp. I've said it before and I'll say it again, we helped. Adam did 90% of the work. It's still good to be singled out. Thanks PASS and thanks to the volunteers at SNESSUG.
Read More

PASS Editorial Board: Blog Directory

PASS
Andy Warren and the PASS Editorial Board have posted their first delivery, a blog directory of SQL Server blogs. That's as complete a list as you're likely to see, anywhere, on blogs related to SQL Server. Well done everyone. This is just the beginning of a new and growing set of services from the PASS organization. If you're not a member, get on over there and join up. It's free. While you're there, get hooked into the  PassPort networking service. Now I need to get more work done on my part of the deliverables.
Read More