DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Header Right

SQL Server Administration

Using DBCC SQLPERF

by 3 Comments

dbcc sqlperfThere are a number of uses for the DBCC SQLPERF command. I touched upon one of them in my last post on how you can use the command to obtain information about transaction log growth.

This handy little DBCC command has been around for a while now. So lets look at the others ways you use it….

  1. Viewing wait statistics data
  2. Clearing wait statistics data
  3. Clearing latch wait statistics data
  4. Obtaining log space size and used data

Syntax and examples for DBCC SQLPERF


--syntax
DBCC SQLPERF(option)
WITH NO_INFOMSGS



--example uses

DBCC SQLPERF(waitstats)

DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)

DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

DBCC SQLPERF(logspace)

Looking at each use case for DBCC SQLPERF in turn….

DBCC SQLPERF(waitstats)

There are newer ways to do this by reading from sys.dm_os_wait_stats but it can be used to obtain wait statistics from your SQL Server.

I wrote about waits stats and wait types in an earlier post so have a read of that if you want to know more about them.

DBCC SQLPERF(‘sys.dm_os_wait_stats’ , CLEAR)

This resets the wait statistics data that your SQL Server has collected since it was last restarted – very handy for performance tuning.

DBCC SQLPERF(‘sys.dm_os_latch_stats’ , CLEAR)

Latches are internal resource locks and wait data is recorded in a similar way to wait type data. To clear out the latch waits that your SQL Server has collected since it was last restarted then run this command.

DBCC SQLPERF(logspace)

Finally, in order to get log space data on how large your transaction logs are and what percentage of them is in use then execute this command. My last post was about this dbcc command, you can view that here.

Permissions needed for DBCC SQLPERF

In order to run this command, your user needs VIEW SERVER STATE permission on the server.

Monitoring SQL Server Transaction Log Growth Using DBCC SQLPERF(logspace)

by 5 Comments

monitor transaction log growth with dbcc sqlperf(logspace)For this article, I am using DBCC SQLPERF(logspace) to get size information about the transaction logs. I hope you will find this post useful in helping to monitor the growth of your transaction logs.

The idea here is to capture and store the growth information at set intervals. This can provide a way to report on how much the logs are growing by and when. Reports are then delivered by an automated process.

If you issue DBCC SQLPERF(logspace), you will see output similar to the following:

dbcc sqlperf(logspace)

The output is self explanatory where for each database you can see the size of the transaction log and how much of it is in use currently.

So this is useful information on its own and even more useful if it can be captured on a regular basis and reported on.

So I wrote some T-SQL to do this and wrapped it up inside a stored procedure.

Firstly I will create a table to store the output of DBCC SQLPERF(logspace).

CREATE TABLE dbo.TransLogMonitor
(
LogID INT PRIMARY KEY IDENTITY(1,1),
LogDate DATETIME NOT NULL DEFAULT(GETDATE()),
DatabaseName VARCHAR(100) NOT NULL,
LogSizeMB DECIMAL(18, 2) NOT NULL,
LogSpaceUsed DECIMAL(18, 2) NOT NULL,
[Status] INT NOT NULL
)

CREATE INDEX IX_LogDate ON TransLogMonitor(LogDate);

Now this is the bit where we capture the output.

INSERT INTO TransLogMonitor(DatabaseName, LogSizeMB, LogSpaceUsed, [Status])
EXEC ('DBCC SQLPERF(logspace)')

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(5 row(s) affected)

Now run the code and then have a look at the data in your new table and it will look very similar to the output from running DBCC SQLPERF(logspace) to screen but with a couple of extra columns for the primary key values and the date that the data was inserted. The LogDate values are obviously very valuable enabling you to report on the data within any date range.

So lets say I wanted a report made available each day which gave me a comparison between yesterday’s and today’s growth, you could write something like this having the data collector running once a day.

DECLARE @Yesterday DATE = CONVERT(DATE, GETDATE()-1);
DECLARE @Today DATE = CONVERT(DATE, GETDATE());

SELECT COALESCE(Y.DatabaseName,T.DatabaseName) AS DatabaseName
, T.LogSizeMB AS TodayLogSizeMB
, Y.LogSizeMB AS YestLogSizeMB
, T.LogSizeMB - Y.LogSizeMB AS SizeMBDiff
, T.LogSpaceUsed AS TodayLSpaceUsed
, Y.LogSpaceUsed AS YestLSpaceUsed
, T.LogSpaceUsed - Y.LogSpaceUsed AS UsedDiff
FROM dbo.TransLogMonitor Y
FULL OUTER JOIN dbo.TransLogMonitor T ON T.DatabaseName = Y.DatabaseName
WHERE CONVERT(DATE, Y.LogDate) = @Yesterday AND CONVERT(DATE, T.LogDate) = @Today
ORDER BY TodayLogSizeMB DESC

Returning this output:

dbcc sqlperf(logspace)

Delivering the DBCC SQLPERF(logspace) report

Providing that you are running a version of SQL Server that is able to send email then you could deliver the report via this method. This example has attached the output as a text file but you can also format it up nicely as HTML if you want to.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB1'
, @recipients = 'your smtp email address goes in here'
, @subject='Transaction Log Space Consumption Report'
, @query = 'EXEC DBName.dbo.usp_stored_procedure_name'
, @attach_query_result_as_file = 1

To wrap this up….

Once you have the code written, it could be scheduled to your preference. As I mentioned, this example is to run daily and compares yesterday and today but you could run it hourly if you wanted to and then write some T-SQL to analyze the hours of the day when you get the most growth. You could write daily reports for the whole week, something to report on the month etc. It could be a great report to help with capacity planning.

In order to deliver this, you could send the report by scheduling with SQL Server Agent, an SSIS package, Reporting Services or SQLCMD called from a batch file using windows task scheduler.

It’s really up to you 🙂

References

DBCC SQLPERF – link to documentation is here
sp_send_dbmail – link to documentation is here

An Introduction to SQL Server Backup

by 2 Comments

sql server backup databaseBefore I get into this post on SQL Server backup, I must make something really clear without sounding like I am stating the obvious. 😐

Having a fully tested backup and restore plan in place for your SQL Server databases is one of, if not, THE most important job that you have to ensure you get right as a DBA.

Getting it wrong could mean huge financial costs to your company, potentially leaving your companies reputation in tatters and leaving you out of a job.

The important thing to bear in mind is that your database backups are only good if you can restore them correctly. If you do not have a tested disaster recovery plan in place, don’t put it off any longer, get one setup and test it regularly.

SQL Server Backup types

There are a number of different ways to backup your database and I will expand on these in later posts. For now here is a summary:

  • Full – a complete database backup which truncates the transaction log of inactive records
  • Differential – a backup of all of the changed data pages since the last full backup. Usually smaller than a full backup, assuming that not all pages have changed
  • Log– transaction log backup containing all transactions since the last transaction or full backup. Also truncates the log of all inactive log records
  • File – a way to backup individual database files
  • Filegroup – a way to backup a group of files contained inside of a filegroup
  • Copy-Only – a backup which can be taken without disrupting the log chain. Great for taking a copy of a production database for development purposes
  • Mirror – allows you to backup to more than once device simultaneously
  • Partial – similar to filegroup but will backup the primary, all read/write filegroups and optionally, read only filegroups

SQL Server Backup – which recovery model should you choose?

This is an important decision to make when setting up your databases. The recovery model determines whether you can perform point in time recovery or not. It also affects transaction log size.

There are 3 types of recovery model:

  • Full
  • Simple
  • Bulk-Logged

Full recovery model

If your database is enabled with a full recovery model, it means that the transaction log will only be truncated upon completion of a FULL backup or upon completion of a transaction log backup.

This recovery model is necessary for log shipping, database mirroring and transaction log backups to provide point in time recovery.

Point in time recovery is vital if you cannot tolerate much data loss. How often you backup your transaction log will determine how much data you can recover in the event that you have a disaster.

Backing up the transaction log is far superior than setting up differential backups every 30 minutes as a differential will backup all the changed data pages taken since the last full backup. This could take some time to complete and consume lots of disk space.

In comparison, a transaction log backup will simply backup the transactions written to the log since the last log backup – a much quicker and less disk space hungry operation 🙂

It is advised that if you implement the FULL recovery model, that you do also put in place a policy to backup your transaction logs regularly. Failing to do this on a busy system could mean that your transaction log disks run out of disk space very quickly!

Simple recovery model

With simple recovery, the transaction log is truncated on each checkpoint.

What this means is that if a transaction is committed to disk, the space used by that transaction inside of the transaction log, is emptied leaving space within the transaction log file.

If you are not concerned about point in time recovery or any of the other processes associated with the full recovery model, then this is a handy option to enable and requires less management of the transaction logs because the log is always truncating.

Bulk logged recovery model

Finally we have the bulk logged recovery model. For this option, it is still possible to backup the transaction log but it does not permit point in time recovery. The advantage with this recovery model is that it uses minimal logging in the transaction log.

This is not only faster because less is being written to the log but it also means that the transaction log will not grow as large compared with the full recovery model.

If the business requirements allow you to switch from full logging to minimal logging, you might choose to enable this during maintenance windows when index rebuilds are happening. This will help to reduce the transaction log growth and upon completion, switching back to the full recovery model for example.

You can make this switch because the log chain is intact. The bulk logged recovery model will preserve the log chain and not require you do take another full backup. However as the transactions recorded during the time when the database was in bulk recovery are only minimally recorded, you cannot restore your database to a point in time when the bulk recovery model was enabled.

Only a restore point before or after the recovery model was enabled will be possible for point in time recovery.

As the transactions could suffer data loss under this recovery model, it is advisable to only make this switch during periods when the users are not updating the database or when you are able to recover the transactions which were otherwise lost by some other means. So for example, you might have an SSIS package importing a bunch of files which could easily be re-imported.

Scheduling and retention of your SQL Server backup

It’s possible to implement scheduling of your backups using the SQL Server Agent. What you choose to implement will depend on the data retention requirements for your business. A very simple example of a schedule might look like this:

  1. Take a weekly FULL backup
  2. Take daily DIFFERENTIALS
  3. Take regular transaction log backups, for example every 30 minutes.

This schedule might repeat each week to a point when the backups are a certain age, they would no longer be kept in storage and re-used. This will depend on your business requirements.

In SQL Server, backup schedules can be put in place which are customized to your needs and available backup capacity. The available options provide great flexibility as everything can be done at the database level.

You may have databases which are critical to the business which need frequent backups and you may also have databases which do not change, for example read-only databases. You may only back these up very rarely in this instance but retain those backups for long periods of time.

Backup storage – what to use

I can think of some options:

  1. Backup to local disk
  2. Backup to network share or SAN
  3. Backup to tape
  4. Combination of disk/network/SAN plus tape – you may keep aged backups on tape and recent backups on disk for example.
  5. Backup to cloud – there are companies now which provide this service.
  6. Backup to FTP location

Ensure that whatever option or options to decide upon that there is redundancy there to help protect your backups.

A word on backing up to local disk

Where I work, we have a 3rd party solution which takes care of our backups. It authenticates to the SQL Servers and takes regular backups to a large SAN. Those backups are then copied to tape and the tapes are taken offsite.

The key to this is that the backups are stored external to the SQL Servers and not locally. The issue with local backups is that if your server dies for any reason, you can potentially lose your backups and you are in trouble.

You can backup locally and it could perform better than backing up over a network for example but you’ve got to make sure that you have copies of those backups made somewhere else to safeguard your data.

A word on backing up to tape

Similar to local disk backups, these backups are only as good as the tapes and device you put them on. So ensure you have copies made of your backups. I personally would not put a backup solution in place which consisted purely of tape backups.

A word on database snapshots

It’s possible to take a snapshot of a database in SQL Server. This is a great way of taking a cut of the database which you might choose to do some reporting on for example. It’s also possible to revert a database back to a snapshot however do not rely on snapshots for your database backups.

Snapshots exist on the same database server as the source database. If you lose your drives, you lose your source database and database snapshots! You may as well then go clear our your desk and hand in your security pass. 😐

SQL Server backup software

Now it would be foolish of Microsoft not to supply you with the tools to do this and thankfully, it has long been possible to completely manage your backup and restores using SQL Server Management Studio and the SQL Agent for scheduling.

The tools which are available to you do a pretty good job but as your backup requirements grow, you may decide it is time to invest in some backup software to help manage your SQL Server backups.

I’m going to be looking at some backup software for SQL Server in a future post.

Summary

I hope this provides you with some information on where to start with your SQL Server backup plan. In future posts, I will be going into more of the how to aspects of running backups and restores inside of SQL Server. For more information you can visit this link

How to Delete Millions of Rows using T-SQL with Reduced Impact

by 21 Comments

In this post, I talk about deleting millions of rows in SQL Server whilst keeping impact low.

Deleting millions of rows in one transaction can throttle a SQL Server

TRUNCATE TABLE – We will presume that in this example TRUNCATE TABLE is not available due to permissions, that foreign keys prevent this operation from being executed or that this operation is unsuitable for purpose because we don’t want to remove all rows.

When you run something like the following to remove all rows from your table in a single transaction,

DELETE FROM ExampleTable

SQL Server sets about the process of writing to the transaction log all of the changes to be applied to the physical data. It will also decide on how it lock the data. It’s highly likely that the optimizer will decide that a complete table lock will be the most efficient way to handle the transaction.

There are potentially some big problems here,

  • Your transaction log may grow to accommodate the changes being written to it. If your table is huge, you run the risk of consuming all the space on your transaction log disk.
  • If your application(s) still requires access to the table and a table lock has been placed on it, your application has to wait until the table becomes available. This could be some time resulting in application time outs and frustrated users.
  • Your transaction log disk will be working hard during this period as your transaction log grows. This could be decreasing performance across all databases which might be sharing that disk for their transaction logs.
  • Depending on how much memory you have allocated to your SQL Server buffer pool, there could be significant drops in page life expectancy, reducing performance for other queries.
  • The realisation that a big performance issue is occurring lends temptation to kill the query. The trouble with that is it can delay things even more as the server has to rollback the transaction. Depending on how far along the operation is, this could add on even more time to what was originally going to be.

For example, if you kill the query and it is 90% done then the server has to rollback a 90% completed transaction. This will vary but the rollback can take as much time as the delete operation was in progress! (check using KILL n WITH STATUSONLY)

Some ways to delete millions of rows using T-SQL loops and TOP

Use a loop combined with TOP and delete rows in smaller transactions. Here are a couple of variations of the same thing. Note that I have arbitrarily chosen 1000 as a figure for demonstration purposes.

SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP (1000)
FROM LargeTable
END

And another way…

DoItAgain:
DELETE TOP (1000)
FROM ExampleTable

IF @@ROWCOUNT > 0
GOTO DoItAgain

These are simple examples just to demonstrate. You can add WHERE clauses and JOINS to help with the filtering process to remove specifics. You would add error handling/transactions (COMMIT/ROLLBACK)  also.

Summary

It’s a bad idea to delete millions of rows in one transaction 🙂 and whilst this might sound like a no-brainer, people do try and do this and wonder why things start to go bad.

Breaking the delete operation down into smaller transactions is better all round. This will help reduce contention for your table, reduce probability of your transaction log becoming too large for its disk and reduce performance impact in general.

Your transaction log disk will still be working hard as your refined delete routine removes the rows from your table. Try and run this task during maintenance windows which are typically done inside off peak periods.

Resetting table identity value using DBCC CHECKIDENT

by Leave a Comment

DBCC CHECKINDENT can be used to reset a tables identity value on a column.

On the official Microsoft page about it, it reads

“Checks the current identity value for the specified table in SQL Server 2016 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.”

This was an interesting find but when you might you use it?

I will create a small table to demonstrate how to use this.

T-SQL:

CREATE TABLE Table_1
(
ID INT IDENTITY(1,1) NOT NULL,
DateAdded DATETIME NOT NULL
)

Insert some rows….

INSERT INTO Table_1(DateAdded)
VALUES(GETDATE());
INSERT INTO Table_1(DateAdded)
VALUES(GETDATE());
INSERT INTO Table_1(DateAdded)
VALUES(GETDATE());
INSERT INTO Table_1(DateAdded)
VALUES(GETDATE());
INSERT INTO Table_1(DateAdded)
VALUES(GETDATE());

Check the current value which produces the below output….

DBCC CHECKIDENT ('Table_1', NORESEED);

Checking identity information: current identity value ‘5’, current column value ‘5’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now we will reset the identity value so that the next time we insert data, the value will be 10….

DBCC CHECKIDENT('Table_1', RESEED, 9);

Checking identity information: current identity value ‘9’, current column value ‘5’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Add another row and check identity value. The row inserted will have a value of 10….

INSERT INTO Table_1(DateAdded)
VALUES(GETDATE());

Checking identity information: current identity value ’10’, current column value ’10’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Scenarios when you might use DBCC CHECKIDENT

It’s already clear what the function of this is but when might you use it?

I’m nervous at the thought of this being called in a production environment but in a dev environment, sure it could come in handy if you need to reset things to a known state.

TRUNCATE TABLE is a command which will enable you to delete all rows from a table and at the same time, reset the identity value. However you need permission to be able to do this and it will only work if the table is not referenced by a foreign key constraint. This is when DBCC CHECKIDENT is useful.

Some notes about DBCC CHECKIDENT

  • You need to be a db_owner (dbo) of the database. If you are not a dbo, you would need permission to execute on behalf of dbo using WITH EXECUTE AS
  • Reseed as (n – 1). So if you wanted a value of 5 as your next value, reseed using 4
  • Setting a value which is less than values which are already in the table will result in unique constraint violations as soon as the value inserted hits a value which already exists in the table.
  • Data type limits apply, you cannot exceed the max value of a TINYINT for example when reseeding.
  • Running it on an empty table produces a change in behaviour. The following demonstrates this….
DBCC CHECKIDENT('Table_1', RESEED, 0);

Now after running that DBCC statement and then adding a row, you would expect the next value to be a 1 but it’s not….

INSERT INTO Table_1(DateAdded)
VALUES(GETDATE());

DBCC CHECKIDENT ('Table_1', NORESEED)

Checking identity information: current identity value ‘0’, current column value ‘0’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is normal behaviour however. If the table is empty, then the next value is the reseed value as opposed to the reseed value plus the increment value.

Using sp_change_users_login to fix SQL Server orphaned users

by 15 Comments

I’m going to be looking at sp_change_users_login as a continuation to a previous post where I looked at a couple of ways to transfer logins from one SQL Server to another and touched upon the issue of the orphaned “security identifier” (SID).

A typical scenario that arises is when the DBA quickly realises that the logins on the SQL Server cannot access the database. They try and add the login to the database as a user and are presented with the error:

Error 15023: User already exists in current database.

sp_change_users_login to the rescue!

I first saw this error a number of years ago and due to my complete lack of experience at the time, one of my first thoughts was that I would have to remove the database users, re-add them all for each login requiring access and then proceed to add the permissions back in for user.

I quickly realised that this would be a massive waste of my time and that there had to a better way and so I proceeded to consult the search engines for a resolution. Unsurprisingly I quickly found many other people who had been in the same situation as me and that sp_change_users_login had been the cure to all their woes.

And here I am writing a post about it 🙂 Well I never would have imagined that but it was a long time ago and only clever people could put a website together back in those days when blogging platforms were a twinkle in some programmers eye.

How to use sp_change_users_login to fix SQL Server orphaned users

Firstly, there may be a number of orphaned users, so the best thing to do is run this inside each database you are checking:

USE DatabaseName
EXEC sp_change_users_login 'Report';

You will see output like the screenshot attached if there are any orphaned users. In this example, user “db_login1” is showing up as an orphaned user.

sp_change_users_login report

If you already have a login which you want to map your database user to, you could run the following (note that the first instance of ‘db_login1’ is the user in the database, the second instance is the login to be mapped to) :

EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';

If you don’t already have a login to map to, you can have sp_change_users_login create one for you and with a password. The following code does this and creates a login with the same name and a password of ‘aaZZww77’ as an example.

EXEC sp_change_users_login 'Auto_Fix', 'db_login1', NULL, 'aaZZww77';

sp_change_users_login auto_fix

For more info on this process, click this sp_change_users_login link to view the documentation from Microsoft.

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close