SQL Server Query Execution Plans in SQL Server Management Studio
Learn about SQL Server Query execution plans in SSMS and how to generate actual and estimated plans and how to use them to tune queries.
Tim Ford is a Senior Database Administrator with MindBody in San Luis Obispo, California and is in the process of relocating west to the Pacific Northwest from Michigan. Since 2010 he’s produced Microsoft Data Platform training events branded as SQL Cruise from Alaska to the Caribbean and the Mediterranean at Tech Outbound, an events company specializing in technical training in unconventional locations. His SQL Cruise events take place on cruise ships in the Caribbean, Alaska, and the Mediterranean. Tim also is the Executive VP of Marketing for PASS, the global association for Microsoft data professionals. He also is a contributing author for itprotoday. Tim loves helping people find their true potential through education and building networks between Thought Leaders in various fields and those who are just starting on their careers or struggling to find their footing in established careers. If you’re looking for this sort of experience then check out the next SQL Cruise event taking place this August in Seattle.
- MSSQLTips Awards: Acheiver (75+ tips) - 2010
SQL Server 2005 Index Best Practices
In continuing with our series of tips on Best Practices for SQL Server I'm turning my sights on Maintenance. Specifically in this tip we will be discussing Index maintenance: when, if, why, and how are questions that will be addressed. Many tips here at
Using Windows Groups for SQL Server Logins as a Best Practice
We are taking a break from the tips format this month to provide you with some Best Practice articles for the many aspects of Microsoft SQL Server development, administration, and support. This article will focus on the use of Windows Groups for security
Session State Settings for Cached SQL Server Query Plans
A cached query plan will not be used if the query is different than the original query used to generate the cached plan - makes sense - but what if the query is identical? Well even then the query optimizer may not used the cached query plan. Why? Beca
Find, compare and use the same session settings as another SQL Server user
When diagnosing issues in SQL Server I've found that sometimes I need to be able to mimic a user's session state when attempting to repeat an error they may be receiving. The smallest differences can completely change the outcome, so I need to ensure all
Third Party Application Checklist for SQL Server
It is a pretty good bet that if you are a Database Administrator you are dealing with third-party vendors who have supplied databases for you to support within your environment. Just about all of us have to deal with a mix of in-house-developed and exter
Find Current Running SQL Statement in SQL Server
In this article we look at a TSQL statement you can use to isolate the currently running part of a batch to help pinpoint what is running and assist with troubleshooting.
Identify last statement run for a specific SQL Server session
I was reading a recent blog post regarding returning information on the latest query executed for a given session. The post offered up a couple options to return the last query statement executed, settling upon querying the sys.sysprocesses system compat
Retaining historical index usage statistics for SQL Server Part 3 of 3
In this, the third of the four part series on persisting the rowset results from the indexing Dynamic Management Views cached in temporary internal SQL Server structures we will explore what is required to store the missing index-related metadata. Part o
List columns and attributes for every table in a SQL Server database
In this tip we look at scripts that will list all columns and attributes for every table in a SQL Server database.
SQL Server Date function that determines date range based on weekday
Occasionally I find myself dusting off my old developer coffee mug and sitting down in front of my laptop to do a bit of creative coding when one of our current developers runs up against something they can't get their head around. Recently this was the
Retaining historical index usage statistics for SQL Server Part 2 of 3
In part one of this series of persisting Dynamic Management View data that is stored in the cache and sourced from the Query Optimizer we examined the process for persisting data for the sys.dm_dm_index_usage_stats DMV. That DMV stores usage metrics (as
Using the OBJECT_SCHEMA_NAME function to return the SQL Server schema name
With the introduction of schemas in SQL 2005 more and more DBAs are taking advantage of them versus having everything owned by dbo. In this tip I will show you a new system function that has been delivered with SQL Server to get a handle on the schemas
Retaining historical index usage statistics for SQL Server Part 1 of 3
Starting with Microsoft SQL Server 2005, DBAs were able to have a greater insight into the inner workings of their supported SQL Server instances through the use of Dynamic Management Views and Functions. The problem with some of this data is the values r
SQL Server function to convert MSDB integer value to time value
In a recent tip I outlined a process for converting a date, stored as an integer into a datetime data type. Date and time information for run history of SQL Server Agent jobs is stored within the msdb..sysjobshistory table as an integer data type, not as
SQL Server UDF to pad a string
Unlike other relational database management systems that shall remain nameless, SQL Server's underlying coding language, T/SQL, does not have a built-in function for padding string values. I recently took it upon myself to create my own and as you'll see
Handling cross database joins with different SQL Server collations
Recently I had to create a SQL Server instance with a non-standard collation (or as the vendor that created the database solution we were implementing called it non-default, which gave me a chuckle that they were arguing the matter.) Now that the colla
SQL Server function to convert integer date to datetime format
In this tip we look at code you can use to convert an integer date into a datetime format. This is an issue you will face when working the history data for SQL Server Agent Jobs.
How to find out what SQL Server rights have been granted to the Public role
One of the first security issues that forced adoption of SQL 2005 SP3 came in December 2008, when news broke that a vulnerability to Microsoft SQL Server could be exploited via the sp_replwritetovarbin stored procedure. This entire situation started me t
How to eliminate SQL Server security issue caused by sp_replwritetovarbin
The February 2009 Microsoft Security Bulletin included an updated Executive Summary (MS09-004) referring to a highlighted vulnerability with Microsoft SQL Server allowing an authenticated user to escalate privileges to those of SYSTEM, thereby taking cont
Manage the size of SQL Server Transaction Logs
I've mentioned on a number of occasions I support an insane (I-N-S-A-N-E) number of databases and SQL Server instances. Doing so makes it difficult to pay as much attention to all aspects of your environment as you may need. In a perfect situation the D
Getting additional error messaging information for SQL Server replication
In this tip we look at how to get additional information related to SQL Server replication errors for troubleshooting.
Auto notification for failover or a restart of SQL Server services
Our clustered Microsoft SQL Server environment hosts over 200 databases currently across all nodes. Whereas clustering technology has been around for quite some time now, many applications still are not cluster-aware. The problem we encountered was with
Identify Windows Volumes on Clustered SQL Server Instance with DMVs
I have inherited a Microsoft SQL clustered server and I don't know much about it's logical structure. It is a two node, active-active cluster and I am trying to determine which of the logical volumes on the shared disk are associated with each SQL Server