Category Archives: SQL Server

High Availability Lab: Adding dual Ethernet NIC yields big improvements

Posted on 8. November 2017 by admin

I have a lab that includes: Two standalone SQL Server 2014 instances One standalone SQL Server 2017 instance running on Linux Thee 2016 instances that are part of an availability group A 2016 failover cluster instance with two nodes A … Continue reading →

Posted in AlwaysOn, SQL Server | Tagged , , | Comments Off on High Availability Lab: Adding dual Ethernet NIC yields big improvements

WAN Emulator for Availability Group Testing

Posted on 21. October 2017 by Bennett

I had an unmet need to be able to test my AlwaysOn Availability Groups under less than ideal network conditions.  More specifically I needed a way to add network latency in order to create more realistic monitoring and troubleshooting scenarios. … Continue reading →

Posted in Open Source, SQL Server | Tagged , , , , | Comments Off on WAN Emulator for Availability Group Testing

How to quickly and easily simulate a low disk space scenario

Posted on 14. October 2017 by admin

It turns out that the built in fsutil OS utility can quickly create an empty file of any size.  For example: fsutil file createnew test.txt 1300000000 Will create a file 100 billion bytes in size. For my scenario I needed to … Continue reading →

Posted in Performance, SQL Server | Tagged , , | Comments Off on How to quickly and easily simulate a low disk space scenario

AlwaysOn Availability Group Aware SQL Agent Job Step

Posted on 6. May 2017 by Bennett

The SQL Agent job step that I present here is intended to make a job execute only if the node currently owns a specified replica. The T-SQL for this job step leverages a function that was published in SQL Server … Continue reading →

Posted in SQL Server, T-SQL, Uncategorized | Tagged | Comments Off on AlwaysOn Availability Group Aware SQL Agent Job Step

Collecting Database File Stats with PowerShell

Posted on 11. February 2017 by Bennett

Here is a fairly simple PowerShell script that can be used to collect database file statistics across multiple servers. My version of the script is incorporated into a scheduled SQL Agent job that runs daily. It is very useful for … Continue reading →

Posted in Powershell, SQL Server | Comments Off on Collecting Database File Stats with PowerShell

Leveraging SP_Whoisactive for Monitoring and Troubleshooting BI/DW Workloads

Posted on 15. January 2017 by Bennett

Wait stats analysis is often my tool of first resort for triaging performance problems. Adam Machanic’s SP_Whoisactive utility is my tool of choice for getting insight into what is happening on a server. Data Warehouse ETL processes often occur overnight. … Continue reading →

Posted in SQL Server, T-SQL | Comments Off on Leveraging SP_Whoisactive for Monitoring and Troubleshooting BI/DW Workloads

Missing Index Stats Enhancements

Posted on 8. April 2016 by Bennett

Here is my script to find missing indexes using the missing index DMVs. It’s pretty much like others with the exception that I first get the instance uptime and then use that to calculate the number of seeks and scans … Continue reading →

Posted in Performance, SQL, SQL Server | Comments Off on Missing Index Stats Enhancements

Partition-Level Lock Escalation

Posted on 5. October 2013 by Bennett

I ran into a situation at work where partition lock escalation solved a particular problem.  A table, with about 300 million rows, has a primary key that contains something called a ProcID or Process ID. The process ID is tied … Continue reading →

Posted in SQL Server | Comments Off on Partition-Level Lock Escalation

Free SQL Server training videos

Posted on 16. February 2011 by admin

The people over at SQLSkills.com have made their MCM training videos available online for free.  They’re pretty incredible.  Subject areas include database internals and performance, high availability and disaster recovery, performance tuning,  security and development support.  Link here:  http://www.sqlskills.com/T_MCMVideos.asp   … Continue reading →

Posted in Performance, SQL Server | 1 Comment

Alert for long-running SQL database backups

Posted on 23. August 2010 by Bennett

One of my daily tasks is to do a quick check of each SQL Server instance using Activity Monitor, sp_who2, or a DMV-based script.  Sometimes I get busy and forget to do this task.  Today I broke down and wrote … Continue reading →

Posted in SQL Server | 1 Comment

Learning SMO & Powershell

Posted on 7. April 2010 by Bennett

I created a small script to collect SQL Server file utilization data into a repository.  I’m collecting the space used by the files as well as the space used internally by SQL Server.  I’m doing this across all production databases … Continue reading →

Posted in Powershell, SQL Server | 1 Comment

Index to Filegroup mapping

Posted on 28. January 2010 by Bennett

Here is a trivial script to show where a particular index resides. It saves clicking around the SSMS GUI. 1 2 3 4 5 6 7 SELECT i.name, i.type_desc, i. is_primary_key, i.is_unique, s.name AS [Filegroup] FROM sys.indexes i INNER JOIN … Continue reading →

Posted in SQL Server, T-SQL | Comments Off on Index to Filegroup mapping

Quick and Dirty CSV import to SQL Server

Posted on 11. June 2009 by Bennett

You can use LogParser (a free, unsupported utility from Microsoft) to quickly import a CSV file into a SQL Server table using syntax like this: logparser “SELECT * INTO MyTable FROM d:\MyFile.csv” -i:csv -o:SQL -server:MyServer -database:MyDatabase -createTable:ON There are many … Continue reading →

Posted in SQL Server, Windows OS | Comments Off on Quick and Dirty CSV import to SQL Server

Getting data file space usage

Posted on 2. February 2009 by admin

Here is a simple query to get space usage by data file:  SELECT     a.FILEID,     [FILE_SIZE_MB] = CONVERT(decimal(12,2),ROUND(a.size/128.000,2)),     [SPACE_USED_MB] = CONVERT(decimal(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)),     [FREE_SPACE_MB] = CONVERT(decimal(12,2),ROUND((a.size–FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) ,     NAME = LEFT(a.NAME,24),     FILENAME = LEFT(a.FILENAME,55)     FROM dbo.sysfiles a 

Posted in SQL Server, T-SQL | Comments Off on Getting data file space usage

Useful Multipliers

Posted on 28. January 2009 by Bennett

Multiply SQL database pages by 0.0078125 to get space in Megabytes Multiply SQL database pages by 0.00000762939453125 to get space in Gigabytes Here are a couple of examples of where this is useful: This query uses a DMV to return … Continue reading →

Posted in SQL Server, T-SQL | Comments Off on Useful Multipliers

One-off backups in SQL Server 2005

Posted on 7. January 2009 by Bennett

I am frequently asked to refresh development databases with production data.  The usual way to do this is to  back up the production database and then restore over the development database.  SQL Server 2005 has a new backup option, “WITH … Continue reading →

Posted in SQL Server | 1 Comment

SQL Server “Max Server Memory” Config Value

Posted on 6. October 2008 by Bennett

I learned an interesting tidbit while reading Inside Microsoft SQL Server 2005: Query Tuning and Optimization.  The Max Server Memory configuration value applies to the cache buffer pool and not SQL Server as a whole.  Here is a link to … Continue reading →

Posted in SQL Server | Comments Off on SQL Server “Max Server Memory” Config Value

Shrinking all log files

Posted on 18. August 2008 by Bennett

I’m not a big proponent of shrinking files. There are a lot of postings on DBA forums citing why this practice should be avoided. Tibor Karzai in particular has an article on why this is a bad idea. Shrinking log … Continue reading →

Posted in SQL Server, T-SQL | Comments Off on Shrinking all log files

Stripping the time component from a datetime value

Posted on 7. April 2008 by Bennett

Here is a very simple and elegant way to strip the time component from a datetime value: CAST(FLOOR(CAST(@date AS float)) AS datetime) Written as a function: CREATE FUNCTION BareDate ( — Add the parameters for the function here @date datetime … Continue reading →

Posted in SQL Server | Comments Off on Stripping the time component from a datetime value

Partition Misalignment can Cause Poor Performance

Posted on 1. April 2008 by Bennett

Here is an interesting Microsoft Knowledgebase article titled Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000. It explains how a partition misalignment can cause significant … Continue reading →

Posted in Performance, SQL Server | Comments Off on Partition Misalignment can Cause Poor Performance

Basic Database Inventory Script

Posted on 4. March 2008 by admin

CREATE TABLE #tempresults2 ( database_name NVARCHAR(128), backup_start_date DATETIME ) INSERT INTO #tempresults2 SELECT database_name, backup_start_date FROM msdb.dbo.backupset AS b1 WHERE backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb.dbo.backupset AS b2 WHERE b1.database_name = b2.database_name AND TYPE IN (‘D’, ‘I’) ); WITH … Continue reading →

Posted in SQL Server | Comments Off on Basic Database Inventory Script

Query to get last database backup dates

Posted on 19. February 2008 by admin

Here’s a simple query to find the last backup dates:   SELECT backup_start_date, database_name FROM msdb.dbo.backupset AS b1 WHERE backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb.dbo.backupset AS b2 WHERE b1.database_name = b2.database_name AND type = ‘D’ )

Posted in SQL Server | Comments Off on Query to get last database backup dates

SQL Server Best Practices Analyzer (BPA)

Posted on 24. January 2008 by Bennett

A new version of the SQL Server 2005 Best Practices Analyzer (BPA) is here. On a related note, the BPA reported several operating system issues that I was unaware of, including an issue where SQL Server’s working set gets paged … Continue reading →

Posted in SQL Server, Windows OS | Comments Off on SQL Server Best Practices Analyzer (BPA)

First stab at a database inventory script

Posted on 22. January 2008 by Bennett

Compatible with sql server 2k5: USE master CREATE TABLE #tempresults ( [name] sysname, db_size NVARCHAR(13), [owner] sysname, [dbid] smallint, created NVARCHAR(11), [status] NVARCHAR(600), compatibility_level tinyint ) INSERT INTO #tempresults EXEC sp_helpdb SELECT db.name, tr.db_size, db.compatibility_level,is_auto_shrink_on, state_desc, recovery_model_desc, page_verify_option_desc FROM sys.databases … Continue reading →

Posted in SQL Server, T-SQL | Comments Off on First stab at a database inventory script

Code Prettifier

Posted on 14. January 2008 by Bennett

I found an easy way to render code snippets in HTML. It is called the Simple Talk Code Prettifier. In addition to rendering, it also converts SQL keywords to uppercase and gives you proper indentation.  Also supports VB.  http://www.simple-talk.com/prettifier/default.php By … Continue reading →

Posted in SQL Server | Comments Off on Code Prettifier

Searching for a column name in a SQL database

Posted on 9. January 2008 by Bennett

Here are a couple of different ways to search for a column name within a SQL Server database: — using information_schema views (preferred method) SELECT sc.table_name    FROM information_schema.columns sc    INNER JOIN information_schema.tables st      ON sc.table_name = st.table_name … Continue reading →

Posted in SQL Server, T-SQL | Comments Off on Searching for a column name in a SQL database

Script to Return Space Used for All Tables in a Database

Posted on 7. May 2007 by Bennett

Here’s a handy script to return space used by table:  spaceused.html

Posted in SQL Server, T-SQL | Comments Off on Script to Return Space Used for All Tables in a Database

Troubleshooting SQL Server with Filemon

Posted on 13. February 2007 by Bennett

Today I came across an interesting problem while changing the SQL server service to run on a non-administrative domain account. Previously the service was configured to log in as localsystem, which has full rights locally, but no domain rights. With … Continue reading →

Posted in SQL Server | Comments Off on Troubleshooting SQL Server with Filemon