Thought I'd share a cool trick I picked up a while back with the OVER() clause. Typically you’d use this clause with the PARTITION BY or ORDER BY arguments with the ranking functions released with SQL Server 2005. The OVER() clause though can also be used to do aggregations with a SELECT statement without a … Continue reading The OVER() Clause
Updated content at - Really Search Cache For Execution Plans Sometimes when I am working with clients I need to take a look at execution plans for different stored procedures. Rather than digging around with SQL Profiler or executing the stored procedures with what I think might be the parameters I like to '”return to the … Continue reading Search Cache For Execution Plans
A few weeks back I was working on a process that would utilize foreign keys to crawl from table to table to export data from a production database to an archive database. More on some of what went into that process probably at some point in the future. When the project was released to production … Continue reading Find Duplicate Foreign Keys
A while ago while making changes to a client database, I needed to determine all of the object level permissions that were in the database I was working on. The system stored procedure sp_helprotect turned out to be pretty useful in obtaining this information. The procedure accepts four parameters: They do pretty much what would … Continue reading Scripting Object Level Permissions
Have you ever needed the definition of a view, stored procedure, or user defined function? I sure hope you have - otherwise reading any more of this posting will be more than pointless. For those of you still with me, have you ever also not wanted to find that definition without have to browse through … Continue reading I Don’t Want To Use the Object Browser (sp_helptext)
I found a nice little feature in SQL Server Reporting Services (SSRS) today. Through the expression "=dataset!<dataset name>.commandtext" the query executed by the dataset can be returned to the report. There is also a rewrittencommandtext function but it appears to only be populated when Report Models are being used. This feature been around for a … Continue reading Are You Sure You’re Using the Right Query?
I'm generally a convert to the congragation that all tables should have clustered indexes. It makes sense, helps performance, and in most cases is the best starting point for a tables. If you aren't part of the community of the faithful on clustered indexes, please read Kimberly Trip's blog on clustered indexes. There are other … Continue reading Find The Heap Indexes
Thought I'd share something I threw together for a client today. They were looking for index a list of indexes in a database with their associated sizes and usage. Using this WHERE statement allowed them to identify 38GB of index space that was being allocated unnecessarily for indexes.
I've been looking into the AUTO_UPDATE_STATISTICS_ASYNC option for SQL Server lately and found a couple decent articles on there on it. AUTO_UPDATE_STATISTICS_ASYNC SQL Server Database Configuration Asynchronous Update Statistics Preventing the blocking of queries in OLTP environment from statastic updates seems like a good thing.
Not a really hard thing to figure out, but for some crazy reason I always forget. So, hopefully actually writing it out for the world to see will help me remember. Since tempdb is created every time that SQL Server restarts, the created date for that database can be used to determine the last time … Continue reading Last Time SQL Server Restarted
Strate SQL 