Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Everyday SQL

Patrick Keisler is a MCTS and MCP. For over 12 years, he has been been a database administrator for a major investment bank, Wells Fargo Securities. During that time, he has gained considerable knowledge in Microsoft SQL Server by supporting hundreds of applications ranging from high-volume trading applications to massive data warehouses.

The Performance Impact to Prefix Stored Procedures with sp_

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…

Read more

0 comments, 378 reads

Posted in Everyday SQL on 19 February 2013

T-SQL Tuesday - 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…

Read more

3 comments, 2,516 reads

Posted in Everyday SQL on 12 February 2013

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…

Read more

1 comments, 2,373 reads

Posted in Everyday SQL on 5 February 2013

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…

Read more

3 comments, 1,507 reads

Posted in Everyday SQL on 29 January 2013

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…

Read more

3 comments, 425 reads

Posted in Everyday SQL on 22 January 2013

Exam 70-432 - SQL Server 2008 Implementation and Maintenance

For years, actually more like a decade, I've been saying that I'm going to get certified in SQL Server.  Well finally after years of saying it, I finally did.  I passed my first exam, 70-432, this past week.  I have to say it was easier and at the same time…

Read more

0 comments, 329 reads

Posted in Everyday SQL on 15 January 2013

SQL Server has encountered % occurence(s) of cachestore flush for the % cachestore (part of plan cache) due to some database maintenance or reconfigure operations

Have you seen this type of an informational message in your SQL errorlog before?  If so, then I hope the timestamp associated with it was not during the middle of your peak processing time.  This is a good indication that someone has been tinkering with some of the SQL configuration…

Read more

0 comments, 185 reads

Posted in Everyday SQL on 7 January 2013

How to Use xp_dirtree to List All Files in a Folder - Part 2

In a previous blog post, I demonstrated how you can use an undocumented stored procedure, master.sys.xp_dirtree, to list all files in a folder.  The previous posting mostly talked about how the stored procedure worked and the output it generated.  For this posting, I have expanded upon the script…

Read more

0 comments, 1,283 reads

Posted in Everyday SQL on 31 December 2012

SQL Saturday #143 - Washington, DC

Last week, I attended my very first SQL Saturday event in Washington, DC.  Although I don't live anywhere near Washington, I was able to make the short trek from Charlotte, NC.  It was well worth the time and effort.  I missed the previous one in Charlotte back in October, but …

Read more

0 comments, 126 reads

Posted in Everyday SQL on 18 December 2012

T-SQL Terminator

You may have noticed in my code examples, I always include a semicolon ";" at the end of my TSQL statements.  If you're not doing this, then you need to start now.  Actually, if you're not writing TSQL code as a DBA then you need to start now.

Starting in…

Read more

0 comments, 167 reads

Posted in Everyday SQL on 11 December 2012

Get Performance Counters from within SQL Server

I think most DBAs know that you can use the Windows Performance Monitor to capture performance metrics for your SQL Server.  But did you know that you also have access to all the SQL Server performance counters from within SQL Server?

By using using the DMV sys.dm_os_performance_counters, you can view…

Read more

0 comments, 183 reads

Posted in Everyday SQL on 4 December 2012

How to Use xp_delete_file to Purge Old Backup Files

Continuing on my recent trend of using undocumented stored procedures, this week I thought we can cover using xp_delete_file to purge old backup files from a folder.  This is stored procedure used by the maintenance plans to clean up old backup files, but it makes for a handy purge tool…

Read more

0 comments, 241 reads

Posted in Everyday SQL on 21 November 2012

How to Use xp_dirtree to List All Files in a Folder

UPDATED -- Dec 31, 2012 -- Be sure to read Part 2 of this post discussing xp_dirtree.

Last week I blogged about how to use an undocumented stored procedures to create folders.  This week we need to do the opposite.  We need to get a list of all files…

Read more

0 comments, 416 reads

Posted in Everyday SQL on 13 November 2012

Dynamically Create a Folder for Each Database

If you're a DBA like me then you're probably pretty detail-oriented and like to keep things very organized.  For example, when doing database backups, I like to have a specific folder structure to hold the backup files for each database.  See below:


In order to make this…

Read more

0 comments, 167 reads

Posted in Everyday SQL on 6 November 2012

Customize the Management Studio Toolbar

Management Studio is great tool with almost everything you need right at your fingertips.  Notice I said "almost".  I like to customize the toolbars to meet my needs of doing work every day.  Because being a DBA means you're busy all day long, and you don't have time to hunt…

Read more

0 comments, 305 reads

Posted in Everyday SQL on 30 October 2012

Capture Index Usage Statistics for Better Analysis

Starting with SQL Server 2005, Microsoft introduced Dynamic Management Views to help DBAs see what's going inside of SQL Server.  One of my favorite is the sys.dm_db_index_usage_stats.  It collects statistics for different types of index operations and the timestamp at which it was last performed.   All the DMVs are great…

Read more

0 comments, 163 reads

Posted in Everyday SQL on 23 October 2012

Which SQL Instance is Killing Your CPU?

You have 5 instances running.  How do you know which one is killing your CPU?

As you can see from Windows Task Manager, you have several instances of SQL installed and one of them is using nearly half of the CPU.  To figure this out, you'll need to know the…

Read more

0 comments, 117 reads

Posted in Everyday SQL on 16 October 2012

Grant Execute Permission on All Stored Procedures

Right out of the box, SQL Server makes it pretty easy to grant SELECT, INSERT, UPDATE, and DELETE to all user tables.  That's accomplished by using the built-in db_datareader (SELECT) and db_datawriter (INSERT, UPDATE, and DELETE) database roles in every user database.  Any user you add to those database roles…

Read more

0 comments, 289 reads

Posted in Everyday SQL on 10 October 2012

How to Make SQL Server Listen on Multiple Ports

Have you ever needed an instance of SQL Server to listen on multiple TCP/IP port numbers but didn't know how to implement it?  Well here's a quick step-by-step guide that will work for SQL Server 2005, 2008, and 2012.

Open SQL Server Configuration Manager.

On the left hand side, expand…

Read more

0 comments, 169 reads

Posted in Everyday SQL on 5 October 2012

Use Powershell to Manage Idera SQL Diagnostic Manager

Idera SQL Diagnostic Manager (or as I call it "SQLdm") is a great monitoring and performance tuning tool that I was introduced to a few years ago.  There are no server-side agents, so it's a very lightweight tool and good for monitoring all SQL Servers in your environment.  The  GUI…

Read more

2 comments, 353 reads

Posted in Everyday SQL on 28 September 2012

Newer posts

Older posts