Calculate Rows Inserted per Second for All Tables

Ever needed to calculate the number of rows inserted every second, for every table in every database on a server? Or, have you ever needed to validate that all processes have stopped writing to tables? These types of questions come up routinely for me. To help with this, I’ve written the following script, which examines metadata values using sys.partitions. This method isn’t as accurate as running SELECT COUNT(*) FROM, but it’s much faster. Keep in mind, since it’s just looking at row counts, it’s not much help on tables that have a lot of update/delete activity. But it does what I need it to do, and I use it pretty regularly, so I thought I’d share in case anyone else can benefit from it too. /* Declare Parameters */ DECLARE @newBaseline BIT = 1 — change to 0 when you don’t want to replace the baseline, i.e. after initial run , @delay CHAR(8) = ’00:00:30′; — change as needed   IF @newBaseline = 1 BEGIN IF OBJECT_ID(’tempdb..#baseline’) IS NOT NULL DROP TABLE #baseline;   CREATE TABLE #baseline ( database_name SYSNAME , table_name SYSNAME , table_rows BIGINT , captureTime DATETIME NULL ); END   IF OBJECT_ID(’tempdb..#current’) IS NOT NULL DROP TABLE … Continue reading

Index Defrag Script, v4.1

It’s been quite some time since my last index defrag script update. A big part of the reason for that is because I wanted to implement many of the suggestions I’ve received, but I just haven’t had the time. I still have those changes planned, but I’m not sure quite when I’ll get to it. Rather than continue to wait for a major release, I’m releasing a small update to my defrag that will take care of the most common complaints I receive. Change Log: Bug fix for databases containing spaces or special characters Support for case-sensitive databases Re-executable CREATE script (for those who want to re-run the whole script) Comma-delimited list of databases is now supported for the @database parameter Feature List: Defrag a single database, a list of databases, or all databases (@database) Time Limitations: stop defragging after the specified amount of time has elapsed (@timeLimit). Please note, it will not kill a defrag that is currently in process, even if it exceeds the threshold. Optional stop-and-resume functionality: pick up where your defrag last left off without having to rescan sys.dm_db_index_physical_stats. (@forceRescan) Defrag scheduling: choose which days to defrag certain indexes, or exclude certain indexes altogether, by using … Continue reading

T-SQL Script for Estimating Compression Savings

A couple of weeks ago, I was working on a Microsoft PDW proof-of-concept (POC) and had to measure compression ratios. In order to do this, I fired up SSMS and wrote a little script. The script will iterate through all tables in a database and run the sp_estimate_data_compression_savings stored procedure. This will only work in SQL Server 2008+ versions running Enterprise edition. If you’re not familiar with this stored procedure, it basically will tell you what effect PAGE or ROW compression will have on your table/index/partition, etc. There are pro’s and con’s with compression. What I’ve tended to see is that compression has very positive results on space, IO, and query duration, with a negative impact on CPU and write speed. Like most things, it’s a trade-off and the results will vary by environment, so I recommend you do some testing before you apply compression to all tables. I tend to use compression mostly for my historical tables and partitions and leave my recent data uncompressed. And, back to the script, I use this stored procedure to estimate the impact of compression and to determine whether to use PAGE or ROW compression. PAGE is a higher level of compression, which … Continue reading

String Parsing Function

This handy little script parses a string and returns the results as a table. I know there are a ton of string parsing functions out there, but I thought I’d add to the list. The basic logic of it (using a CTE) is derived from a forum post I found years ago. The table-valued UDF, delimiter, etc. is all stuff that I added. So thus, while I can not claim complete credit, I still thought it’d be worthwhile to share. Plus this will be used in another script I will be posting soon. /* Let’s create our parsing function… */ CREATE FUNCTION dbo.dba_parseString_udf ( @stringToParse VARCHAR(8000) , @delimiter CHAR(1) ) RETURNS @parsedString TABLE (stringValue VARCHAR(128)) AS /********************************************************************************* Name: dba_parseString_udf   Author: Michelle Ufford,   Purpose: This function parses string input using a variable delimiter.   Notes: Two common delimiter values are space (‘ ‘) and comma (‘,’)   Date Initials Description —————————————————————————- 2011-05-20 MFU Initial Release ********************************************************************************* Usage: SELECT * FROM dba_parseString_udf(<string>, <delimiter>);   Test Cases:   1. multiple strings separated by space SELECT * FROM dbo.dba_parseString_udf(‘ aaa bbb ccc ‘, ‘ ‘);   2. multiple strings separated by comma SELECT * FROM dbo.dba_parseString_udf(‘,aaa,bbb,,,ccc,’, ‘,’); *********************************************************************************/ BEGIN   /* … Continue reading

11-Word Warning

Tom LaRock posted a new Meme Monday challenge: “Write a SQL blog post in 11 words or less.” Donabel Santos tagged me, and I couldn’t resist the challenge. So here’s my entry: Hasty coding, error prone. No backups, data loss. Company for sale. This was inspired by the recent spate of stories I’ve heard about companies that have failed because they did not properly manage their data and databases. I don’t know who’s been tagged or not, so I’m gagging some of my SQL Saturday Chicago friends: Ted Krueger Jes Borland David Stein

Identity Columns: Are You Nearing The Limits?

I use identity columns frequently. After all, identity columns make great clustering keys. But it’s important when using identity columns to check on the amount of values you have left before you reach the limit of your data type. An identity column has a fixed amount of values it can use based upon whether you specified tinyint, smallint, int, or bigint when you defined the column. If you reach this limit, your inserts will blow up and cause a Chernobyl-like SQL meltdown will begin to fail. I just finished an audit of my tables and thought I’d share the script. I would like to warn that this script is *not* perfect… namely, it doesn’t handle negative integer values very elegantly. It also doesn’t know if you started your seed at zero, approached your max positive limit, then reseeded to the negative limit (see my “quick and dirty fix” tip at the end of this article). /* Define how close we are to the value limit before we start throwing up the red flag. The higher the value, the closer to the limit. */ Declare @threshold decimal(3,2) = .85;   /* Create a temp table */ Create Table #identityStatus ( database_name … Continue reading

Effective Clustered Indexing

My first Simple-Talk article was published yesterday! I’m pretty excited about it and wanted to share the link. In the article, I give an overview of how clustered and nonclustered indexes work, and I demonstrate why clustered index best practices — narrow, unique, static, and ever-increasing — are important design considerations. You can find the article on Simple-Talk’s website at: Please let me know your thoughts!

SQL Agent Job Script

This blog post is brought to you by the awesomeness that is the SQL Server Twitter community. Contributions were made by several awesome Tweeples, including Denny Cherry, Kendra Little, Ken Simmons, and Magnus Ahlkvist, among others. What started this is something you’ve all probably run into at one time or another. We’re currently having some resource contention on our server when a ton of processes kickoff and overlap in the morning. Now, I have a script that I’ve used in the past for monitoring SQL agent jobs, but this time I wanted to add some historical run-time information. I know the sysjobhistory table contains the information I need, but it has some… let’s say, creative ways of storing the data. Opinions on the reasons why vary: Regardless the reason, I needed to do some conversions. Denny shared with us the msdb.dbo.agent_datetime function to convert run_date and run_time into an actual datetime value. I have to say, this certainly cleans up the code quite a bit! Then Magnus shared a method to convert run_duration into seconds, which he modified from a script on SQLServerCentral. I was able to use these two tidbits to update my old script. You can now run … Continue reading

Disposable Indexes

Today I had to run an ad hoc query on a 8.5 billion row table. The table had a dozen columns of a variety of data types and was clustered on a bigint identity. There were no other indexes on the table. My query involved a join to a smaller table with a date range restriction. Without an adequate index to use, SQL Server was going to be forced to scan this 8.5 billion row table. Now, I don’t have much patience for waiting for long running queries. I want to run the ad hoc, e-mail the results, and forget about it. But short of adding a nonclustered index, which would take a very long time to build and probably require additional space requisitioned from the SAN team, what could I do? Enter disposable indexes. Now, you might be asking yourself, “What the frilly heck does she mean by a disposable index? Is that new in Denali?” No, dear reader. I am actually referring to filtered indexes, which is available in SQL Server 2008 and 2008 R2. I call them “disposable” because I create them to significantly speed up ad hoc queries, then I drop them when I’m done. Here, … Continue reading

Yet Another PASS Summit Recap & Thoughts on PDW

The SQL blogosphere has been lit up with PASS Summit recaps. I debated about whether or not to write my own post, until I remembered that this blog serves as a mini-journal for me too. I have a notoriously poor memory–my husband likes to say that my CPU and memory are good, but I must have an unusual clustering strategy–so maybe this blog post will be a good pointer for me when I start prepping for next year’s Summit. This was definitely the best PASS Summit conference ever. While there will always be opportunities to do things better–improvement is a never-ending process–it was clear that the organizers of this event listened to the feedback they had received the previous year. One of the best changes? Backpacks. These were very useful, as evidenced by their presence everywhere. Nice job, organizers! My absolute favorite thing about Summit is the chance to meet and reconnect with so many amazing SQL folks. There were entirely too many people to list out, but some highlights include meeting Crys Manson, Jorge Segarra, and Karen Lopez for the first time. I also had a chance encounter with Ola Hallengren in the Sheraton elevator. Apparently we were only … Continue reading