Press "Enter" to skip to content

Category: Administration

Row Counts For All Tables

Andrew Peterson has a script to get row counts for each table based on sys.dm_db_partition_stats:

When you need the row count from a table, the most common way is to use count(*). But what if you need the row count for all the tables in your database?  You could use the sp_MSforeachtable stored procedure:

EXEC sys.sp_MSforeachtable ‘select ”?”,count(*) from ?;’;

Nice, but the return format is a bit difficult to manage, since it effectively runs the query for as many times as you have tables in your database.

Click through for Andrew’s script.  One thing to keep in mind is that the number of rows might be off, especially for columnstore tables with ghost records.  It’s an estimate, but one which tends toward the correct answer.

Comments closed

Functions To Know By Heart

Kevin Hill has a top ten functions list for database administrators:

3. DBCC Opentran – log file filling up?  Processes blocked and timing out? See if some doofus (hopefully not you) left a transaction open and went to lunch.  If you are lucky you can kill it.   Unlucky and you find an undistributed replicated transaction.

4. Getdate() – Not sure why, but this winds up in probably 80% of the scripts I write.  Not surprisingly, it often brings along it’s little brother, DateDiff.

I’d go with GETUTCDATE() or SYSUTCDATETIME() over GETDATE(); that way, everything gets stored as UTC and you spend less time worrying about time zone or Daylight Savings Time issues.

1 Comment

Logging Space Used

Andrew Peterson has a script to grab space used for each database:

After reviewing the posts, many, perhaps all of the procedures and commands I found provided valuable information. Just not what I wanted. We have:

sp_spaceused, but it returns data in two data sets. Not ideal.  Next we have…

sp_databases, but it just returns a list of databases and the size. Not ideal.  Or use…

DBCC showfilestats,   Ok, but still incomplete. And, since it is not listed on the main DBCC page at MSDN, its most likely deprecated.

And speaking of DBCC, there are other commands to consider. But what I was really hoping to find was a simple way to use the sys.database_files DMV. It is very close, but not quite complete.

Read on for the solution.  One small change I’d prefer is using Aaron Bertrand’s sp_foreachdb for iterating.  Or write a Powershell script if you want to take the looping outside of SQL Server.

Comments closed

Understanding sp_who2

Kendra Little explains what the sp_who2 procedure does and how to read its results:

Let’s talk about sp_who2

I started out using sp_who2, also! And I was often confused by sp_who2.

sp_who2 is a built-in stored procedure in SQL Server.

  • Shows a lot of sessions, even on an idle instance
  • Doesn’t tell you much about what it’s doing

Here’s what an idle SQL Server looks like in sp_who2

This is my dev SQL Server instance. I have a few sessions open, but the only one which was executing anything was the one you see, where I ran sp_who2. I don’t have any agent jobs scheduled on this thing. It’s just waiting for something to happen.

It’s hard to know what to look at, because we see so much. And only 19 of the 49 sessions is on the screen, too.

Kendra then goes on to explain that there are better ways of getting this information and plugs sp_whoisactive.  I’m 100% in agreement.

Comments closed

Instant File Initialization In DMVs

Rodney Landrum shows off a couple new columns in SQL Server 2016 SP1 DMVs:

Microsoft announced many new features in SQL Server 2016 SP1 and the fanfare was mostly centered around the Enterprise features now available in SQL Server 2016 Standard Edition.  Many may have missed some hidden gems in the announcement.  Two of these are columns added to the existing DMVs, sys.dm_server_services and sys.dm_os_sys_info. The columns provide information for two specific features that previously had to be gathered by opening gpedit.msc and/or scrolling through SQL error logs. I am referring to Lock Pages in Memory and Instant File Initialization (enabled via Perform Volume Maintenance Tasks privilege).

It is now possible to simply query the DMVs to determine if these are being used for the running SQL Server instance.

Click through for the details.

Comments closed

Using ostress

Erik Darling explains how to stress test using ostress:

You can spend a lot of money on people and complicated software to design, run, and monitor workloads against test environments, or you can throw together tests with some free tools like SQL Query Stress or Microsoft’s RML Utilities.

RML Utilities is pretty cool, and includes ostress.exe along with some other components. What I really like about ostress is that it’s all CLI, so you can call it from Agent Jobs, and feed it all sorts of cool options. When you create the Agent Job, you just use the Operating system (CmdExec) type of step instead of T-SQL, or whatever else.

For simulated load, ostress is powerful.  For “ripped from the headlines” load, the Database Experimentation Assistant might do the trick once it matures.

Comments closed

Maintenance Plan Updates

Kevin Hill looks at maintenance plan updates in SQL Server 2016:

True to my typical post style which focuses on small shops, accidental DBAs, and junior DBAs I went looking for something that could very easily benefit people that are using the basic SQL Server features.  In this case they may not even realize how limited they were.

I chose to write about Index Maintenance in the built-in Maintenance Plan portion of SQL Server.

A brief summary of the built-in Maintenance Plans is that they allow you to drag-and-drop your way to basic SQL Server maintenance items such as Backups, Index maintenance, CheckDB, Statistics updating, etc.  This a tool that has been around since at least version 7 that I know of.  It wasn’t always very good, and it gets a bad rap from a lot of DBAs.  It has been dramatically improved over the years in flexibility and reliability.

Read on for the changes.  I’m really not a fan of maintenance plans, but if they’re going to exist, they should at least be as good as possible.

Comments closed

DBCC Checks For Large Databases

David Alcock gives a couple methods for performing consistency checks against gigantic databases:

One way to achieve this is to  split up the consistency checks covering smaller objects and native functionality allows us to do just that, we can perform the checks at the table level or indeed if they are implemented at the filegroup level too using the DBCC CHECKFILEGROUP command.

How to go about this is pretty straightforward; take the list of tables, split them into equal(ish) groups. The groups now form a pool of objects and within a nightly (or daily) window perform the check on each object in the pool. This effectively spreads a database consistency check over multiple days, you avoid the impact on production activities but also ensure all objects are checked over time.

Read on for the solution.  I’m also a big fan of Minion CheckDB, which is designed to handle this type of scenario as well.

Comments closed

Wall Clocks Lie

Lonny Niederstadt digs into some strange behavior on one of his VMs:

Rats.  That was a really good idea, too.  Nothin’.  Or….
(I want to call attention to the change in maximum axis value for CPU utilization.  I nearly *always* use 100% as the maximum value, In these last two graphs I’ve used a maximum value of 24% because utilization of each of the 4 vcpus is really low and I felt it made the graphs slightly more readable.)
Hey!! Perfmon was collecting 30 second samples.  But two intervals after 2:06:22 is 2:07:52 rather than 2:07:22.  The system lost 30 seconds in the span of a minute.  And two intervals past 2:07:52 is 2:11:22 – the system lost 150 seconds in a minute?

It would appear that there are a couple of issues with this box, one of which is a lack of appreciation of the current time.

Comments closed