SQL SERVER Missing Index Information and Query Stats Grant Fritchey put together a nice little query to pull XML query plans out of the DMVs. Thankfully, these plans can also tell use which indexes are missing Scalar Functions vs. Table Valued Functions vs. Inline Code Aaron Alton does some testing on these three to determine which is the most performant. DEVELOPMENT Reducing the cost of getting a stack trace Exceptions are expensive.

Denis Gobo started up a new meme about your first computer and favorite games. My first computer was a Tandy 1000SX with 640KB of RAM that my family got when I was 9 or 10, I think. My dad quickly overclocked it to an astounding 7.6 MHz of pure Intel 8088 power and added a 20 MB hard drive. For the time, it was an amazing computer. My two favorite games were Elite and Starflight.

I recently became even more lazy than usual and was looking for a way to test all of my stored procedures (there are over 180 of them in my production database). Since we have a limited hardware and software budget, buying extra software isn’t a part of the equation. So, I took it upon myself to put together a solution. My first task was to figure out how to accomplish this.

I was debugging a query that was giving me some really strange results. Without pasting the query here, basically it does some interesting math to compute the estimated amount of greenhouse gases produced in tons of CO2, N2O, and CH4. So far this is pretty simple, right? Well, depending on the type of location we have different possible queries and the results are merged with a union and then summed. This is still pretty standard.

SQL SERVER Update Statistics Before or After an Index Rebuild? Colin Stasiuk talks about when you should update stats in relation to rebuilding/reorganizing indexes. I’m not just linking to this because I was the catalyst for his blog post, but because there are some great things in here and I learned a lot from it. Best Practices for installing SQL Server service packs, hotfixes, cumulative updates Beatrice Nicolini put together a great list of best practices for keeping your SQL Server installations up to date.

Things You Know Now… Colin Stasiuk (@BenchmarkIT) has tagged me in his recent blog post Things You Know Now… Here are some of mine, in no particular order of importance or relevance: Accept Failure Failing is a part of live. Well, I prefer to think of it as not succeeded, but that’s a story for a different blog post that I’m never going to write. Anyway, the key is to accept the fact that you’re going to make mistakes.

SQL SERVER Tomorrow’s Microsoft BI Platform Derek Comingore (SQL Server MVP) gives a great overview of the Kilimanjaro release of SQL Server. This is a solid overview of the pieces and parts that will make up the 2010 feature pack release before the release of SQL 11 (in 2011). Top 10 SQL Server 2008 Features for the Database Administrator Mike Weiner and Burzin Patel put together a great list of features for DBAs in SQL Server 2008.

In the first two installments of my articles on SQL Server security, I describedserver level roles and flexible database-level roles. This final article talks about row-based security, specifically row-based security using stored procedures. Why would you want to use row-based security? Well, there are a lot of situations where you simply can’t use flexible roles and/or server level rows. When you have a web application that potentially has thousands upon thousands (if not millions) of users, it doesn’t make sense to create a SQL Server account for each user and have to maintain all of those users.

I finally got sick of not having a calendar table, so I went ahead and made one today. It’s nothing special and it really didn’t take much time, but I thought I would go ahead and share my script in the hopes that google will index it and somebody won’t have to think about it:``` CREATE TABLE [dbo].[Calendar]( [Date] [datetime] NOT NULL, [FirstDayOfMonth] [datetime] NOT NULL, [LastDayOfMonth] [datetime] NOT NULL, PRIMARY KEY CLUSTERED ( [Date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] ) ON [PRIMARY] GO

I have to run similar code on a regular basis, so I thought I would share it with everyone. Basically, it grants access on every stored procedure that meets a certain naming pattern to a single user.``` -- I’ve updated this with Aaron Bertrand’s suggestions from the comments. – Thanks to Aaron for helping make this better! DECLARE @sql AS NVARCHAR(MAX); DECLARE @newline AS NVARCHAR(2); DECLARE @user_name AS NVARCHAR(100); DECLARE @sproc_name_pattern AS NVARCHAR(10);