Last week I ran across a blog post by Axel Achten (B|T) that outlined a few reasons why you should not use SELECT * in queries. In the post, Axel used the SQLQueryStress tool by Adam Machanic (B|T) to stress-test a simple query using SELECT * and SELECT col1, col2,… This gave me an idea to use the same SQLQueryStress tool to benchmark a stored procedure that’s prefixed with sp_. All DBAs know, or should know, you should not prefix stored procedures with sp_. Even Microsoft mentions the sp_ prefix is reserved for system stored procedures in Books Online. I’m not going to discuss the do’s and don’ts of naming conventions. What I want...
Continue reading...Patrick Keisler
T-SQL Tuesday #39 – Use Powershell to Restore a Database on a Different Server
T-SQL Tuesday – This month’s party is hosted by Wayne Sheffield (blog|twitter), and the topic is about Powershell and how to use it for anything SQL Server. With that challenge, I’d like to share a script I’ve written that takes a backup file from one server, copies to another server, and and then restores it. That may sound pretty easy, but I’ve added in a few requirements to the restore. Here’s the scenario: We have two SQL Servers, one production (TRON2R2PROD) and one test (TRON3R2TEST), and we have one user database (AdventureWorks2008R2) on each of the production and test servers. The...
Continue reading...What is DBCC SHOW_STATISTICS Telling Me About My Data?
Per Books Online, DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. Basically it shows you the statistics, or a summary of the data, that SQL Server will use to help generate an execution plan. In the example below, we’ll be looking at the statistics for the index IX_Person_LastName_FirstName_MiddleName, which is a non-clustered index on the Person.Person table in the AdventureWorks2012 database. We’ll cover several queries you can run against your data to help you visualize what the statistics are telling you about your data. Before we dive into the example, we need to update the statistics with a full scan...
Continue reading...How to Remove (Undo) Table Partitioning
I have seen plenty of articles and blog posts out there for how to setup and implement table partitioning, but very few for removing or undoing it. So I thought I would cover a few ways to accomplish this while still preserving the data. There could be many reasons for removing partitioning: no longer needed, need to change partitioned tables, etc. But for our example, we just want to completely remove it from all tables. It would be nice if we could just drop the partition function and partition schema and SQL Server would handle all the rest, but it just...
Continue reading...sp_PerformanceCounters – Get a Health Check of SQL Server’s Performance
During your career as a DBA, you’ll run across articles by SQL experts or other DBAs that just give you an idea. A few years ago I found an article, or a poster to be more precise, that explains SQL Server performance counters. It was written by Kevin Kline, Brent Ozar, Christian Bolton, Bob Ward, Rod Colledge, and Raoul Illyaos. http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf I was thinking this would be a nice poster for any DBA to hang up in their cubical. But as I read down near the bottom, I saw they also mentioned getting performance counters from within SQL Server via sys.dm_os_performance_counters. ...
Continue reading...