25Feb 2009 by Grant Fritchey No Comments

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
23Feb 2009 by Grant Fritchey 34 Comments

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
19Feb 2009 by Grant Fritchey No Comments

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
18Feb 2009 by Grant Fritchey 24 Comments

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
12Feb 2009 by Grant Fritchey 11 Comments

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
11Feb 2009 by Grant Fritchey No Comments

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
11Feb 2009 by Grant Fritchey No Comments

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
10Feb 2009 by Grant Fritchey No Comments

Silly SQL Server 2008 Management Studio Trick

SQL Server
This one is pretty minor and came up in the forums at SQL Server Central. You may have noticed that the Registered Servers window is tabbed with the Object Explorer window. In SQL Server 2005, they were in the same window, stacked on top of each other. To get back to the "good old days" configuration, you can right click on the Registered Servers tab and select Floating. This will cause the window to detach and appear somewhere on your screen. Right click on it again and select Dockable. Now drag it on to the Object Explorer window and it should settle in on top. Ta da, back with the old GUI.
Read More
10Feb 2009 by Grant Fritchey No Comments

Things you know now 2

Misc
I can't believe I forgot this: Test, Test, Test And when you're done testing, test some more. You can't assume that you know how something works. You need to know how it works. Test it. Verify your database design by testing. Check your queries by testing them. Be sure the new index works by testing it. Test the new deployment process before you announce it to the development (way before). When you get done with all this testing... Start testing again.
Read More