Administration

Whats running on my SQL Server? Part 1

2009-09-23T23:27:00Z

SQL Server 2005 introduced some exciting new tools for viewing what statements are running on your database instance. At the base of these tools are the new dynamic management views (DMV) that allows you to view SQL statements executing based on connection. Management Studio uses these views to supply the end user with some pretty informative reports for executions. Today Im going to take a look a couple of the views that are the cornerstone of acquiring this information.

The view I'll look at today is sys.dm_exec_requests. This view returns one record for each request that is executing inside of the database engine. I can query this view with a filter of any session_id > 50 to return all user-level sessions. This view returns a lot of performance data related to the executing script. For example, I can view the status of the command, if it is currently being blocked, the reads and writes incurred by the statement and the query plan. One great thing about this DMV is that it contains a hash field (sql_handle) that contains the actual SQL statement that is being executed. This is very useful because it lets me determine the exact SQL statement that is being called.

SELECT rq.session_id, rq.start_time, t.text 
FROM sys.dm_exec_requests rq
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id > 50

In the above script, I use the sys.dm_exec_sql_text DMV (function) and pass the the sql_handle field to it. This DMV returns the actual SQL text being ran. If I wanted to, I could easily rearrange the above SQL snippet to replace the DBCC INPUTBUFFER command that we are all accustomed to using to determine what SQL a current spid is using. I'll show you how you can easily rewrite DBCC INPUTBUFFER in a future post so that it is a little bit more useful...

Fix for: Cannot open user default database. Login Failed.

2009-09-23T01:59:00Z

In this article I will show you some options to recover from the error "Cannot open user default database. Login Failed".

How does this happen? This happens when a login has a default database set, and that database is deleted. When you open any of the Microsoft database tools (like SQL Server Management Studio or Query Anaylzer), it automatically attaches you to the default database. The problem is, that if that database has been deleted, you get the following error message.

There are a few things that you can do to recover from this. You can login with a different account and change the default database for the effected login. You can do this in one of two ways. Via T-SQL and the SSMS interface.

Step 1) Right click the effected user and choose properties.

Step 2) Change the default database to a different database. The default for all newly created users is master unless a different database is defined so that's the safest choice.

Now what you probably know already is that SSMS will turn around and execute SQL commands to do this. So let's just show you now how to do this.

exec sp_defaultdb 'myuser', 'master'

But what if you don't have a different user that can login and change the account for you? Or, you don't have an alternative tool that will allow you to specify the database at login time. The command line tools for SQL Server will allow you to specify the database which you want to execute. You will use the same command as above. Let's go over it.

First open the command prompt.

Next, if you don't have the SQL Server binn folder in your %PATH% then you will need to navigate to the location where you installed the SQL Server client tools. By default, it is "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn>" where 90 is the folder for the version of SQL Server installed. Then, you will use the command line (osql.exe or isql.exe) to issue the command to change the default database.

Since the osql command line allows you to specify the -d option to specify the database, you can initiate the command in the master database (or any other database that you still have access to).

SQL Authentication

osql -Sservername\instancename -Umyuser -Pmyuser -dmaster -Q"sp_defaultdb 'myuser','master'"

Windows Authentication

osql -Sservername\instancename -E -dmaster -Q"sp_defaultdb 'myuser','master'"

NOTE: There will be no output since the sp_defaultdb
system procedure does not have any output for non-errors.

Now that you have changed the default database, you will be able to login to the database using the standard Microsoft tools with no errors.

The permissions granted to user 'someDomain\someUser' are insufficient for performing this operation. (rsAccessDenied)

2009-09-17T10:06:00Z

This is a very short blog post related to the administration of the SQL Server Reporting Services. This particular post is related to an installation of SQL Server 2008 reporting services on Windows Vista.

Problem: After attempting to access the http://localhost/reportserver the first time after SQL Server installation on Windows Vista, the SQL Server Reporting Services show the following error:

"Reporting Services Error: The permissions granted to user
'Adelso-PC\Adelso' are insufficient for performing this operation.
(rsAccessDenied) Get Online Help".

Solution:

1. Open your SSMS as an administrator. (This is very IMPORTANT! Right-click on the SQL Server Management Studio icon and then click "Run as Administrator")

2. Connect to Reporting Services...

3. For Server Name field type in localhost

4. After you are connected to the Reporting Server, right-click the Home directory and click Properties.

5. Click on "Add group or user", type in the user name in the following format: someDomain\someUser

6. Check the appropriate checkboxes to give permissions.

This is all.

Sql2005SsmsExpressFacet - How to continue your SQL 2008 installation

2009-08-13T08:51:00Z

Today I faced the following problem:

I wanted to install SQL Server 2008 on my machine, but the installation failed with the error: "The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools."

History: I had installed the Visual Studio 2005, including the SQL Express. After that I had installed the SQL Server 2005. And now I wanted to have an instance of SQL 2008.

Great plan, however it didn't go as smooth as intended.

Solution: I went to Programs and files and uninstalled the SQL Express. The SQL 2008 setup was STILL failing.

After looking at the SQL install logs I found that the actual problem is in the registry.

Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM
Slp: Rule 'Sql2005SsmsExpressFacet' detection result: SQL 2005 Ssms EE installed=True
Slp: Evaluating rule : Sql2005SsmsExpressFacet
Slp: Rule evaluation done : Failed
Slp: Rule evaluation message: The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.

All I had to do is to run regedit, locate the

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM

and rename it.

After this, the SQL 2008 installation will continue.

Managing the Transaction Log for the Accidental DBA

2009-05-27T17:36:00Z

by Mark Wills

Attending one of Rob Farley's seminars the other day, I heard the phrase “The Accidental DBA” and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server... So if you are “The Accidental DBA”, or, simply new to SQL Server then there are a few concepts we can share to make your life easier.

Based on the frequency of questions we see in Experts-Exchange, I thought a 'light' discussion about the Transaction Log would be a good place to start.

Microsoft's SQL Server is a great database. Generally, it can look after itself, but there are one or two aspects that will need your attention. You can interactively access, configure, manage, administer, and develop your databases using SQL Server Management Studio (SSMS). With SSMS, the database administrator (ie you) can administer any of the components that we will be discussing. Now, this article is not an introduction into SSMS, but there are a couple of tools you will need to know. For managing your database, in SSMS, use the Object explorer to right click on your database and then Properties for a range of settings and information, or Tasks to shrink or backup files. Also in Object Explorer is the Management group, expand that for Maintenance plans.

An SQL Database is made up of three types of files, the two main files are primary data file with the extension of mdf and the transaction log file with the extension of ldf. The third is an optional secondary data file and has the extension ndf. As users add information into the database, it is stored in the data file. In turn, those transactions and database modifications can be recorded in the Transaction Log. These files are physical disk files and are built with an initial allocation of disk space. That means, you can reserve a pre-determined size for your database, and then set about filling it up (more about that later).

Now, the transaction log is an extremely important and sometimes neglected part of the database. Its use can mean the difference between data survival or devastation if needing to recover following a database or systems failure.

The way in which your database will use the transaction log is determined by the Recovery Model. The way in which you manage the transaction log is using SQL Backup. The way in which you manage the Backup is using Maintenance Plans. And the nice thing ? It can all be automated ! Phew...

The Recovery Model is a setting on your Database. There are three choices for Recovery Model FULL, SIMPLE and BULK LOGGED. While it is possible to change Recovery Model at any stage, it really should be determined before you create your database. The two most often used are Simple and Full so that is what we will talk about.

If running “SIMPLE” recovery mode then data transactions are minimally logged and automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log. If running “FULL” recovery mode then individual transactions and database modifications are fully logged in the transaction log, but then you will need to manage the log file.

The idea behind FULL recovery mode is that you can recover from a failure up to a point in time by applying transaction log backups to the last full backup. Whereas recovering from a SIMPLE recovery model means going to the last full backup. So, your choice of recovery model needs to be considered in terms of your business dependency on the volume (or existence) of data that can be recovered when restoring from backup.

There are a few backup options including Full backup, Differential Backup and Transaction Log backups using the internal Backup command / facility within SQL. When DBAs talk about a backup, it is typically the SQL backup that they are most likely referring to - not the physical disk backup (which is also a crucial part of your DR plan). When restoring a database, it is the SQL backup that you will need to restore from.

Backups are crucial part of managing your transaction logs in a FULL recovery mode. Regardless or recovery model, you do need to run Full backups, the frequency really depends on the type and volume of activity. For a typical office environment, that would most likely be every night. Then for FULL recovery, run transaction log backups during the day at regular and frequent intervals - most common are multiples of 15 minutes (ie every quarter hour, half hour, hourly etc). That helps keep the transaction log clean by truncating those transactions that are now secured in the transaction log backup and so releases that space (ie inside the file), ready to accept new transactions. In FULL recovery, it is only the Transaction log backups that will make a difference to the content of the transaction log, without them, the transaction log will continue to grow.

There are two other Backup options Differential and Copy Only. Differential backups are smaller and faster to create than the base of a full backup being just the changes since last full backup. Using differential backups can speed up the process of making frequent backups to decrease the risk of data loss. At restore time, the full backup is restored first, followed by the most recent differential backup. Copy Only is a “special” back up normally a once off event that will not impact the state of any other scheduled backup operations.

Really, the frequency with which backups are taken needs to reflect the Business's ability to cope with data loss. The big attraction with full recovery is that it records the transactions in the transaction log which can be (progressively) applied to the last full backup. The greater the frequency, the fewer the transactions (relatively speaking) and so the less resources required to take the backup, and the greater likelihood of recovery to a point in time prior to failure.

Now, with all that activity, how is the DBA meant to manage ? Enter, stage right, the Maintenance Plan. The maintenance plan is part of SQL Server and found in the Management object in SSMS. These plans can be scheduled using SQL Server Agent to run automatically (not in the Express edition, but there are alternatives). The maintenance plan can also perform a variety of other jobs, such as removing old backups, database ‘housekeeping’, email notifications and so on. So, can be almost a case of set and forget (well set and monitor really).

When creating or maintaining a database, you can allocate disk space and can be easily controlled. Consuming disk space can get a little more involved and is largely reactionary to the amount of activity. To help the DBA manage, there are the Database settings to help with sizing the database with Autoshrink and Autogrow. Immediately, without thinking, right now, disable Autoshrink. Having done that, you can then go and find out about Autoshrink and agree that turning it off is really the best thing to do. Autogrow is a bit different. When a database or log file is filling up the allocated space, it will decide at some point that it needs more disk. Autogrow sets the amount of disk that the database files will grow by. Given time, and maturity of the database, the size and growth rates will become more predictable, ideally to the point were the growth is fully managed and Autogrow is an exception.

If you are seeing continual growth in the database files it could mean that you are either shrinking, or not managing the size. It could also be due to not running frequent enough transaction log backups. While Autogrow may happen, it really should be the exception and considered a safety measure to accommodate unpredicted activity. We don't really want the overheads with the disk files to stop, grow, resume processing along with fragmentation that must surely result in trying to acquire additional disk. So, when using Autogrow, make it a reasonable amount so even if it must autogrow, the number of instances is minimal (or non-existant)

If you do set your database to simple recovery, the transaction log is managed for you. But then in the event of a database failure, your only protection is the last full backup, or last full backup and the last differential backup. For a typical OLTP type operation, SIMPLE is ill-advised. If your business depends on the information, then you will want high availability and high recoverability (meaning FULL recovery mode). A Read only database on the other hand like a data warehouse or reporting database probably does not need the same dynamic recoverability because they are typically batch updated (from the “real” transactions) and easy to schedule a full backup after the load, or recover from the original transactional database, so ideally suited to SIMPLE recovery model.

So, if you do not already have them, and it is not the Express edition, then get those backups happening, and use the maintenance plans so it can be scheduled. In that regard, would suggest you create three new maintenance plans.

1) Full backup - is a full backup run each day / night, or really, it depends on type and amount of activity.

2) Transaction log backup - frequently during the day if running FULL recovery mode (otherwise do not worry about it). If not running FULL, you may consider Differential backups

3) Database Maintenance Tasks - run periodically to update statistics, rebuild indexes, manage / resize databases etc - normally run at a period where there are high volume turnovers such as an end of month purge / archive or similar - keeps database running efficiently – could even run weekly over the weekend if you have some downtime available.

So, what happens if we don't ? that's simple, you will have uncontrolled disk consumption and exposure to failure. So, what do I do if that hasn't been done before ? that's also simple, do it now - it is never too late. Or, what if I am running out of disk space already ? Then it is highly likely that you may have to shrink or resize the database files and keep it clean from that point forward.

If you can, run a FULL backup and set up those maintenance plans. Maybe (big maybe) and only momentarily, change to SIMPLE recovery mode until you have your plans in place. This should not take long to set up and is done in SSMS. Changing to Simple, and then change back, do a FULL backup, will give you a “clean” log, but it will not be optimised for size and growth. Ideally you size your database files according to the maximum type of activity that will occur in the time frames / frequency dictated by your transaction log backups. That way, disk can be pre-allocated and then allowed to fill up that allocated space. These files can also autogrow, and so is not of “vital” importance, though, it is not desirable for all kinds of performance and fragmentation issues and ensuing IO performance impacts associated with frequent need to autogrow.

If needed, using SSMS, you can also inspect and shrink your files by right clicking on the database, tasks, shrink, files and a window will popup. There is a drop down selection for looking at either the data file or the log file. The down the bottom, you can select some actions - best to use middle radio button to reorganise and also nominate some space. You do need to allow enough space for the data base to grow (physical allocation of disk, and then starts to use that space with data activity). The logs will typically shrink very quickly. The data files will take considerably longer. But you will only have success shrinking files if they can be truncated. That means you must release internal space before you can release (shrink) physical disk space.

Now, we previously introduced the term truncation in terms of releasing space. Using Full Recovery, and regular transaction logs backups, the log files can be kept clean, but how does it do that ? SQL 2005 and 2008 rely on checkpoint processing after backups to truncate those logs :

Except when delayed for some reason, log truncation occurs automatically as follows:

1) Under the simple recovery model, after a checkpoint.

2) Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

A checkpoint is essential for truncating the log under the full recovery - there might still be other factors that prevent it from releasing space. If needed, explicitly invoke a checkpoint after the backup.

You can check via: select log_reuse_wait, log_reuse_wait_desc, * from sys.databases -- looking for 'checkpoint' in log_reuse_wait_desc.

In FULL recovery, the inactive part of the log cannot be truncated until the log records have been captured in a transaction log backup. So, a moot point trying to shrink files without doing the backups, or releasing some of that internal space first.

Having said all that above, heavily favouring the Full Recovery mode, we had one company in EE who decided on “ease of management”. Running a full backup of their Simple recovery database starting from 8:00am and then every two hours worked perfectly for them. Mind you it was not a huge database, and they processed a lot of parchments so recovery simply meant re-keying information. Really, they should have entertained the notion of differential backups, but it is the model that they chose and could cope with. It is quite a bit different if everything is electronic and you lose access to the source information.

All this should be considered in line with your DR plan which most likely incorporates some wide reaching considerations. And believe it or not, with the higher reliability rates, there is less likelihood of failure, but, the more important the DR plan. Suffice it to say, you may need to restore your database at some point in time, for whatever reason (hardware failure, catastrophic failure, database corruption), and better to be in a position knowing that (if nothing else) the “Accidental DBA” has been able to save the database.

There is a good article at : http://msdn.microsoft.com/en-us/library/ms178094(SQL.90).aspx

And a colleague has written : http://sqlservernation.com/blogs/admin/archive/2009/05/17/why-is-my-log-file-so-big.aspx

Hope you found this article of some benefit... And welcome to SQL Server.

Managing Fragmentation for the Accidental DBA

2009-05-27T17:34:00Z

by Mark Wills

We often here about Fragmentation, and generally have an idea that it is about broken bits, or bad for performance, or at least, is generally not a good thing. But what does it really mean ?

By way of analogy, think of the humble Telephone Directory. It is nicely laid out, sequentially in alphabetical sequence by Name. Think about receiving the next edition, not by replacing the directory, but by giving you the added or altered pages which you append to the end. After a few updates, you will soon find that the end of the directory makes no sequential sense at all and you spend an inordinate amount of time trying to find that elusive phone number. It has become fragmented and your directories performance is most likely measured in terms of how long it takes before it hits the bin.

With SQL Server systems there are two basic areas of fragmentation, Disk fragmentation dealing with physical files, and SQL Database fragmentation primarily dealing with how data is stored inside that physical file. There are a lot of parallels, and we will need to look at both.

Disk fragmentation is more of a problem with the classic IO sub-system architectures, and much less of a problem on the more sophisticated IO system solutions such as SAN. Disk systems are made up of building blocks known as sectors, and collections of sectors known as clusters. When physical files occupy non-contiguous space, or, the growth of a physical file means that it needs to hunt down the next available sector, then it becomes fragmented.

The big issue we often hear about within SQL is Index Fragmentation. But there is also Heap Fragmentation (ie tables without a clustered index or Primary Key) and potentially more of a problem. Those areas of fragmentation we see and discuss in SQL is really all about page management - everything SQL revolves around the humble 8K block known as a page, and groups of 8 of them known as extents - we will probably not get down to that level of detail, other than to be aware of the basic building blocks. Conceptually it is similar to Disk with pages and extents akin to sectors and clusters. Not surprisingly, the next few paragraphs, equally apply to Disk and to SQL fragmentation.

Fragmentation, in all cases, is caused by one of two things. The first is the inability to provide sufficient contiguous space at that point in time when it is needed. The second is Data elements become segmented by occupying non-contiguous or fragmented space due to availability. This inability to provide contiguous space has the biggest impact on performance and in turn, causes two types of fragmentation, "external" and "data" fragmentation.

"External" fragmentation occurs to the free or available space. We want it to be kept contiguously so that data can be written without being broken up into different locations. However, if data segments (such as a sector on disk or a page in SQL) are removed at arbitrary places, then the free space is automatically fragmented. In that case, defragmentation means shuffling all that free space (or moving occupied space) into contiguous segments, and conversely, making the available space nice and contiguous as well.

"Data" fragmentation occurs as a result of external fragmentation, but can also occur within a contiguous allocation. It becomes fragmented as new data elements are written into available spaces, and contiguous blocks of information then contain pointers to the next physically available space. This will happen with volatile data, ie data that undergoes addition, changes, and deletions - such as a database.

There is also "Internal" fragmentation. This occurs when a data element is unable to be stored in such a way that all available space is used to maximum efficiency. The only real impact in that situation is most likely wasted space more so than anything degenerative to performance. However, needing more space to occupy less data does have some impact on caching. An example and possible exception is the use of Fill Factor in SQL where you take control over the page "density" to accommodate inclusions, effectively reserving free space.

Fragmentation can be further affected by the type of access to those data segments (regardless of Disk, Database, Indexes etc). Random access is largely independent on fragmented space, and actually leads towards fragmentation (ie small discrete blocks of data being independently read and/or written). Conversely, Sequential data is hugely dependant on contiguous space and is very much the victim of fragmentation.

For SQL, there are tools to help with the statistics and to manage the various options, but it is of vital importance to learn about the dynamics of the business and the databases in use. For example, a periodically updated, largely "read only" databases like a data warehouse or reporting database will have different requirements on how you manage that database versus a considerably more random database such as a "typical" transactional database. Let's consider our Telephone Directory again, but this time as a database. To use it on line, it becomes hugely random. To print a new book, it will be hugely sequential.

Let us take a moment to reflect on what makes up a database. An SQL Database is made up of three types of file, the two main files are the primary data file with the extension of mdf and the transaction log file with the extension of ldf. The third is an optional secondary data file and has the extension ndf. As users add information into the database, it is stored in the data file. In turn, those transactions and database modifications can be recorded in the Transaction Log.

Transaction Logs are sequential in nature (data continually added in a chronological sequence) whereas Data files are essentially random in nature (individual gets or puts from unrelated simultaneous concurrent users). So, from a physical perspective, the types of files in a database means that they are likely to be affected by fragmentation in different ways. Those differences in file types also influence the placement and sizing of those files onto a disk sub-system, even down to the type of RAID and caching methods being used. Kind of a catch-22 in some regards, but sitting, plotting and planning makes it all pretty mechanical at the disk level.

The database files are simply large physical disk files and are built with an initial allocation of disk space. That means, you can reserve a pre-determined size for your database, and then set about filling it up. When creating or maintaining a database, your allocated disk space can be easily controlled, just a matter of knowing how big. Consuming that disk space can get a little more involved and is largely reactionary to the amount and type of activity.

There are a couple of settings in the Database settings which are meant to help with sizing the database, but are potential catalysts for fragmentation. They are Autoshrink and Autogrow. Immediately, without thinking, right now, disable Autoshrink enough said. Autogrow sets the amount of disk that the database files will grow by. When a database or log file is filling up the allocated space, it will decide at some point that it needs more disk and the database engine will grab the space as prescribed in the Autogrow settings. Given time, and maturity of the database, the size and growth rates will become more predictable, ideally to the point were the growth is fully managed and Autogrow is an exception.

Unfortunately, "shrink database" or "grow database" operations are going to unravel and promote disk fragmentation by subsequently and dynamically changing our carefully planned size for the database, unless they are being very deliberately and very specifically performed. That is why we need to disable Autoshrink, and minimise Autogrow instances. If it does need to Autogrow, then you do need to make that incremental growth worthwhile to minimise the number of times the database is being extended. You can apply different growth factors to the individual database files.

Fortunately, SQL gives us the one tool that has maximum impact to help combat Disk fragmentation by being able to allocate space in advance of its use. It is extremely important to do some homework and look at typical growth and then allocate the space required to accommodate normal / predicted growth that is for both the Data and the Log files that make up the Database. When first starting, you will need to determine (erring on the generous side) the amount of disk space that will be required, and monitor that usage so you can then determine the most appropriate size.

For Databases, because they are simply big files, it is important to manage and resolve disk fragmentation before attempting any resolution of internal SQL fragmentation. Like any building, you need solid foundations before you contemplate furnishing the penthouse. So, create your databases by carefully and deliberately sizing the physical files according to the nature and type of database and associated activity, even to the point of choosing a location (such as different disks and/or multi RAID systems) according to the underlying nature of the individual files of the database. Then once you have defragmented the disk, there will be good continuity of space available and resolves one hugely manageable aspect (and cause) of fragmentation. On that note we will leave Disk fragmentation alone.

OK, we are now (finally) getting into the nitty gritty of SQL itself.

First of all, need to understand that internal SQL fragmentation might not be as much of a problem as you may think, and vice versa, ie a database member maybe more fragmented than statistics might first suggest. If data is largely random, with optimised queries that can achieve a good hit ratio, then there is no real problem. Let's revisit our Telephone Directory Database. For on-line, interactive use, it will be hugely random access and so, fragmentation is not necessarily a problem. To print a new book, it becomes sequential and fragmentation is potentially a huge problem.

While still with our Telephone Directory, let us consider having received the first 2 pages of updates (appended to the end) showing a few new entries per alphabetic letter. Now, the few thousand pages (lets say 2000), is nicely structured, and the last 2 pages contains entries from all over the place. Using our tools, it may not be shown as fragmented (ie 1 page per thousand) but, in reality, doing a sequential scan, or an index rebuild will mean it shuffles nearly all the pages following the first instance of the letter "A" from the update pages, so it really is hugely and "logically" fragmented.

So, while we can get all the statistics on fragmentation, it will be your business knowledge and the performance of various queries that will be the most likely indicators that fragmentation (within SQL) is causing a major problem. In our Telephone Directory, what if we were to only permit lookup and add of new phone numbers - the interaction is only random in nature, and fragmentation per se may not really be a problem, albeit present.

At this stage we really do need to qualify some of those scenarios above. With SQL it is important to understand the difference between a table with a Clustered Index or Primary Key over ones that don't. The former we will call an Indexed Table, and the latter is called a Heap table. An Indexed table provides a few benefits over a heap such as physically storing the data based on the clustered index, the ability to use the index to find the rows quickly and the ability to reorganize the data by rebuilding the clustered index. A heap table has no particular order of data, and must always refer back to the IAM (even for sequential access), and might have non-clustered indexes, otherwise random access is painful. Now, a clustered index has inherent pointer to row-data. Any subsequent indexes can use this pointer or key-value in their own indexes to point to the underlying data. Where is all this index structure is kept - on those basic building blocks called pages. Obviously the more compact and "dense" the index information is on a page, the more data can be pointed to, and similarly, the more contiguous pages the greater the caching in one extent, improving caching, and minimising the probability of swapping out one page for another.

So, what leads to fragmentation ? It all depends on the INSERT, UPDATE and DELETE activity on your tables with your physical data and how it is distributed across the range of index keys. If only ever adding new chronologically correct data, then fragmentation is less likely to occur at the heap or data level - such as the transaction log. The real impact is non sequential activity, especially updates (particularly if updating columns which are also members of an index) and deletes (leaving "space" or wholes in otherwise contiguous data). Just as a side note that is one huge attraction with using surrogate keys such as identities or sequential GUID's as primary keys and clustered indexes - they will be sequential and so reduce one big aspect of fragmentation.

A query often used to identify fragmentation is :

SELECT object_name(s.object_id) as table_name,s.index_id, name, index_type_desc, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS S

INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id

ORDER BY 1,2

That DMV (Data Management View), despite its name, it not just indexes, and really it is a function, not just a view. It will show you a fragmentation percent, obviously higher the percent the more fragmented. But they are statistics and you need to apply your knowledge about the business to help ascertain the impact. There are a couple of other interesting DMVs for indexes that can be used in conjunction to help clarify how data is being accessed. The main one is sys.dm_db_index_operational_stats. That is beyond the scope of this article. It is worth noting that the DMVs and Alter Index statements are now the preferred SQL methods rather than the DBCC commands.

Now, fixing fragmentation for indexes is pretty straight forward. Simply (?) rebuilding or reorganizing the index will fix the problem (more later). Heap is a lot more difficult to fix, but fixable if it has to be... Best possible solution is to simply create a clustered index on either an existing identity, or add an identity column as your clustered primary key, which if you really need to can be dropped afterwards. Otherwise, you will need to get into manipulating or managing the data as a bulk process by either exporting, truncating and re-importing, or copy to a new table and rename etc. Like I said, fixing fragmented heap space can be more difficult.

But you really do need to ascertain not if there is fragmentation, but, if fragmentation is causing problems. You do need statistics on page life expectancy and buffer cache hit ratios as a starting point. Because an index rebuild on a large table can be a vey intense and time consuming exercise and really does need careful planning.

So, what is rebuilding or reorganising an index. Well, we are told that the DBCC options are now deprecated and really need to use the newer functions like ALTER INDEX. The REBUILD is essentially recreating the index. If you are running the Enterprise Edition, you can also rebuild online using WITH (ONLINE = {ON | OFF}), off=table is locked exclusively (the default), on=table can be shared, however, it uses more CPU and it generates a considerable number of locks. Someimes better to consider a REBUILD as being an exclusive task. When rebuilding, you do need to start with clustered indexes, or, use the ALL option rather than specify each index name. REORGANIZE does not require exclusive locks as it is more simply reorganizing the leaf nodes of the index, and so, can be run online.

ALTER INDEX {<index name> | ALL} ON <table name> {REBUILD | REORGANIZE}

According to Books On Line, it is recommended that we consider rebuild when fragmentation if 30% or greater. In reality that is a very conservative number, and a more general consensus is indexes should be rebuilt when fragmentation is greater than 40%. So, that would mean indexes should be reorganized when fragmentation is less than 40%. In reality, I prefer the 80/20 rule. You will need to do something if fragmentation is above 20%. If is under 20%, then reorganize is the most likely option but only if you see performance being impaired.

Anyway whatever threshold you choose, and it may be different depending on individual tables, fixing fragmentation is an orchestrated set of events, and can (ideally) be automated, at least for the SQL parts...

1) Set optimal database size allocations (and locations) by file, do not shrink unless really needed, defrag disk once size has been set (or changed). Look at realistic autogrowth settings.

2) Run SQL backups - Run transaction log backup frequently to help reduce growth factors and release internal space within the transaction log disk allocation.

3) Keep "external" fragmentation down. Reorganise your database moving used pages to the front, releasing unused pages at the end.

4) Use statistics to help identify internal fragmentation and use REBUILD or REORGANIZE. Start with Clustered indexes first, then move on to non-clustered.

5) It can be more efficient to drop indexes and recreate. Beware of any check constraints and foreign keys.

6) Ascertain the thresholds where rebuild is required over reorganise.

7) Set up Maintenance plans to do all the above and have them run frequently enough to manage any possible fragmentation.

8) If you have a convenient time window (such as "we do not normally work on the weekend"), then use that time and only that time to do any rebuilding and reorganizing.

9) If you have periodic processes such as an end of month purge or archive, then schedule this type of activity via maintenance plans to follow the periodic processes.

The last point above is probably the most important to consider. Really what you want to do is to set up an automated maintenance task to run in a time window where you can look at restructures in accordance with business activity and processes (such as end of month purges / archiving historical data). Take snapshots of information - that is where you can gain a lot of insight into your business activity impact on a database.

You can then use and modify those maintenance plans to fix disk allocation, alert disk fragmentation, reorganise pages, rebuild indexes, update statistics in that sequence. After all that has been said, you might not need to fix every reported case of fragmentation, but if you have the window of opportunity, then you can also pretty much eradicate fragmentation from your database to the point where fragmentation is always being proactively managed.

Hope you found this article useful...

Why is my Log File So Big?

2009-05-17T15:46:00Z

So, you've ran into a problem. Everything has been running smoothly on your SQL Server database until recently. You have noticed that your drives are out of space, or when you're browsing the files on your system that the log files (typically with an extension of .ldf) are HUGE...as big as or bigger than your data files. This, of course, doesn't make sense to you since you're doing nighly backups and everything until lately seems to have been working properly. So, what could be the issue?

This is a VERY common issue in the SQL Server world, and typically has a small set of factors that contribute to the "problem", and they're all relatively easy to solve. But, before we can solve the problem, we need do discuss a little bit as to what causes it.

The issue almost certainly stems from the fact that your database is set to the FULL recovery model, which is the SQL Server default for new databases. The other option is that you have a long running transactions and the inactive portion of the log cannot be truncated or that you're using transactional replication or database mirroring and the transactions hasn't been replicated yet. For sake of simplicity, I'll omit these scenarios for this article.

When a transaction occurs, such as an UPDATE to a table, the transaction is first committed to the log file. At this point the transaction is considered committed to the database. At some later point in time, the CHECKPOINT process will commit the changes to data pages on disk. Once the CHECKPOINT process does this, these transactions are now essentially inactive because they've been committed. If your database is in the FULL recovery (or BULK logged for that matter) model, these inactive transactions will stay inside your log file (.ldf) until you do something to clean them up. Performing a FULL or DIFFERNTIAL backup does nothing to remove these inactive tranactions. Removing these inactive transactions can be done basically two different ways. First, performing regular transaction log backups will remove these inactive transactions and place them in the backup file. This is the recommended, and basically only safe way to remove these inactive transactions. The other way, which should be avoided at almost all costs, is to truncate these inactive records by hand. I'll not show you how to do this in this writing because doing so breaks your log chain, which makes it impossible to recovery to a point in time (which is really the reason for using the FULL recovery model). If you've found that you're out of space, and truncation is the only option to get you up and running again, see this article I wrote that mentions a way to do it.

If you find yourself continually having log problems, there are two steps you can take to remedy your situation.

1. Start taking regular log backups. You can adjust your Maintenance Plan to include these log backups, or adjust your backup scripts to do so also. This is the ideal change to make as doing so will allow you to recover your database to a previous state at a point in time before any type of disaster that may occur. As you can imagine, having the ability to do may save your (and your company's) neck someday.

2. Change your recovery mode to Simple. If you're not doing log backups and do not plan to in the future, there is really no advantage for you to have your database in the FULL recovery mode. The difference between the two is that in the SIMPLE recovery mode, the inactive portion of the log is truncated for you automatically. However, you'll never be able to recovery to a point in time using this recovery mode...which is fine if your situation does not warrant being able to do so.

Shrinking The Log
You'll notice that once you do a log backup by hand, or you do a manual truncation of the log, the size of the file has not changed. This is at first somewhat confusing considering you have likely just removed a large number of inactive transaction log records. These inactive records have been removed but SQL Server will not allow the operating system to reclaim this space. To do so, you can use a shrink command to free the space. This is generally not a good idea to do on a continual basis as it can lead to performance issues. The good thing is that if you're doing frequent enough log backups (or your recovery mode is set to SIMPLE), the space of the log file should not become a problem.

Here is how you can shrink your log file if you really need to:

DBCC SHRINKFILE('DatabaseName_Log', 0)

Conclcusion
I hope todays discussion of how transaction log backups work and how you can use them to your advantage. If you have further questions, feel free to post a question in our forums. We would be happy to help you with any log problems you may have.

Enable and Disable Batches of SQL Agent Jobs

2009-04-13T19:13:00Z

In the following system stored procedure I allow a list of SQL Server Agent job names to be passed as an XML document. The procedure accepts the list of Job names, parses them out, and will enable or disable them based on the @enabled parameter passed into the procedure. This procedure is VERY handy for those situations where you need to disable a batch of jobs, and then enable them later on. It saves you from having to remember which jobs you disabled, along with saving you from having to go into SQL Server Management Studio to enable the jobs one by one.

Note: The technique I use to parse the XML in this stored procedure is applicable to SQL Server versions 2005 and later.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*********************************************************************************************
*  Author:  Tim Chapman
***********************************************************************************************/
CREATE PROCEDURE [dbo].[sp_EnableJobList]
(
    @xml XML,
    @enabled TINYINT
)
AS
BEGIN

    DECLARE  tempcursor
    CURSOR
    READ_ONLY
    FOR 

    SELECT name 
    FROM msdb..sysjobs
    WHERE name IN
        (
            SELECT t.c.value('@name', 'varchar(30)')
            FROM @xml.nodes('/jobs/job') t(c)
        )

    DECLARE @name SYSNAME
    OPEN tempcursor

    FETCH NEXT FROM tempcursor INTO @name
    WHILE (@@fetch_status <> -1)
    BEGIN
            EXECUTE msdb..sp_Update_Job
            @job_name = @name,
            @enabled = @enabled

            PRINT 'JOB: ' + @name + ' has been ' + CASE @enabled WHEN 0 THEN 'Disabled.' ELSE 'Enabled.' END

        FETCH NEXT FROM tempcursor INTO @name
    END

    CLOSE tempcursor
    DEALLOCATE tempcursor
END

Here's how you can call the above stored procedure to disable or enable a batch of jobs. In this scenario, I am disabling 3 jobs; Job1, Job2, and Job3. I can just as easily enable them by flipping the @enabled parameter to 1 and run the procedure again.

DECLARE @xml XML
SET @xml = '
    <jobs>
    <job name = "Job1"/>
    <job name = "Job2"/>
    <job name = "Job3"/>
    </jobs>'

EXECUTE [sp_EnableJobList]
@xml = @xml,
@enabled = 0

As you might imagine, using an XML document to send in a list of parameters to a stored procedure has a powerful effect. In this scenario, I am taking a list of jobs and enabling or disabling them. If you play around with the concept, you'll see that there are hunderes of scenarios that this same technique can be appleid to.

Getting Table Row Counts

2009-04-13T18:48:00Z

Here's a quick and easy script you can use in SQL Server 2005 to return the number of rows in a table, along with the index size on the table, the reserved space, and the data space for the table. If you're familiar with the system stored procedure sp_spaceused, this information may seem familiar to you. I took some of this code from that procedure, but am presenting it in such a way so that it works for all tables in the database at the same time. This makes it much easier to query, and the values returned are not in a variable character format.

SELECT
  TableName = OBJECT_NAME(id),
  NumberOfRows = CONVERT (CHAR(11), TheRowCount),
  Reserved_KB = ReservedPages * 8,
  Data_KB = Pages * 8,
  IndexSize_KB = CASE WHEN UsedPages > Pages THEN (UsedPages - Pages)
                       ELSE 0
                  END * 8,
  Unused_KB = CASE WHEN ReservedPages > UsedPages
                   THEN (ReservedPages - UsedPages)
                   ELSE 0
              END * 8,
  SchemaName = s.name
FROM
  (
   SELECT
        id = object_id,
        ReservedPages = SUM(reserved_page_count),
        UsedPages = SUM(used_page_count),
        Pages = SUM(CASE WHEN (index_id < 2)
                         THEN (in_row_data_page_count
                               + lob_used_page_count
                               + row_overflow_used_page_count)
                         ELSE lob_used_page_count
                              + row_overflow_used_page_count
                    END),
        TheRowCount = SUM(CASE WHEN (index_id < 2) THEN row_count
                               ELSE 0
                          END)
   FROM
        sys.dm_db_partition_stats p
   GROUP BY
        objecT_id
  ) a
  JOIN sys.objects o ON a.id = o.object_id
  JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE
  s.name = 'dbo'

How to easily DROP and CREATE foreign key constraints

2009-03-24T18:56:00Z

Here's a helpful bit of code I use a lot to generate the statements to DROP and CREATE all of the foreign key constraints on a database. This is useful for going through and using TRUNCATE on tables that would normally not allow truncation due to the foreign key constraints defined.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*********************************************************************************************
Copyright SQLServerNation.com
*  Author:  Tim Chapman
***********************************************************************************************/
CREATE PROCEDURE [dbo].[sp_ShowForeignKeyObjects]
AS
    SELECT 
        OBJECT_NAME(constid) AS ConstraintName,
        OBJECT_NAME(fkeyid) + '.' + COL_NAME(fkeyid, fkey) AS ForeignKeyObject,
        OBJECT_NAME(rkeyid) + '.' + COL_NAME(rkeyid, rkey) AS ReferenceKeyObject, 
        COL_NAME(fkeyid, fkey) AS ForeignKeyColumn,
        COL_NAME(rkeyid, rkey) AS ReferenceKeyColumn, 
        constid AS ConstraintID, 
        OBJECT_NAME(fkeyid) AS ForeignKeyTable,         
        fkeyid AS ForeignKeyID, 
        OBJECT_NAME(rkeyid) AS ReferenceKeyTable,
        rkeyid AS ReferenceKeyID,
        keyno AS KeySequenceNumber
    FROM 
        sysforeignkeys
    ORDER BY 
        OBJECT_NAME(rkeyid) ASC, COL_NAME(rkeyid, rkey)

GO

Now that I have my helper function defined, I can define a system procedure in the master database to generate the TSQL statements to disable all foreign key constrains and also the TSQL to enable them. This script makes use of the helper procedure I just defined and generates the script to drop all foreign keys on your system and adjoining script to recreate them.


IF OBJECT_ID('tempdb..#FK')>0
	DROP TABLE #FK

IF OBJECT_ID('tempdb..#Const')>0
	DROP TABLE #Const

CREATE TABLE #FK
(
	ConstraintName VARCHAR(255),
	ForeignKeyObject VARCHAR(255),
	ReferenceObject VARCHAR(255),
	ForeignKeyColumn VARCHAR(255),
	ReferenceKeyColumn VARCHAR(255),
	ConstraintID INT, 
	ForeignKeyTable VARCHAR(255),
	ForeignKeyID INT, 
	ReferenceKeyTable VARCHAR(255),
	ReferenceKeyID INT, 
	KeySequenceNumber SMALLINT
)

CREATE TABLE #Const
(
	ConstraintID INT, 
	FBuildField VARCHAR(2000) DEFAULT(''), 
	RBuildField VARCHAR(2000) DEFAULT(''), 
	CountField SMALLINT 
)

INSERT INTO #FK
EXEC sp_Showforeignkeyobjects

SET NOCOUNT ON
DECLARE  tempcursor
CURSOR
READ_ONLY
FOR 

	SELECT 	
		f.ConstraintName ,
		f.ForeignKeyObject ,
		f.ReferenceObject ,
		f.ForeignKeyColumn ,
		f.ReferenceKeyColumn,
		f.ConstraintID , 
		f.ForeignKeyID , 
		f.ReferenceKeyID , 
		f.KeySequenceNumber, 
		f.ForeignKeyTable,
		f.ReferenceKeyTable 
	FROM #FK AS  f
		INNER JOIN 
		(
			SELECT ConstraintID, MAX(KeySequenceNumber) AS MaxSeq
			FROM #FK AS  k
			GROUP BY k.ConstraintID
		)b ON f.ConstraintID = b.ConstraintID AND f.KeySequenceNumber = b.MaxSeq

DECLARE 
	@ConstraintName VARCHAR(255),
	@ForeignKeyObject VARCHAR(255),
	@ReferenceObject VARCHAR(255),
	@ForeignKeyColumn VARCHAR(255),
	@ReferenceKeyColumn VARCHAR(255),
	@ConstraintID INT, 
	@ForeignKeyID INT, 
	@ReferenceKeyID INT, 
	@KeySequenceNumber SMALLINT, 
	@ForeignKeyTable VARCHAR(255),
	@ReferenceKeyTable VARCHAR(255)

OPEN tempcursor

	FETCH NEXT FROM tempCursor INTO 
	@ConstraintName ,
	@ForeignKeyObject ,
	@ReferenceObject ,
	@ForeignKeyColumn ,
	@ReferenceKeyColumn,
	@ConstraintID , 
	@ForeignKeyID , 
	@ReferenceKeyID, 
	@KeySequenceNumber,
	@ForeignKeyTable ,
	@ReferenceKeyTable 

WHILE (@@fetch_status <> -1)
BEGIN

	DECLARE  tempcursor2
	CURSOR
	READ_ONLY
	FOR 
	
		SELECT ConstraintID, ForeignKeyColumn, ReferenceKeyColumn, KeySequenceNumber 
		FROM #FK 
		WHERE ConstraintID = @ConstraintID

		ORDER BY ConstraintID, KeySequenceNumber ASC
	
	DECLARE @ConstraintID2 INT, @ForeignKeyColumn2 VARCHAR(255), @ReferenceKeyColumn2 VARCHAR(255), @KeySequenceNumber2 SMALLINT
	DECLARE @FKeyBuildField VARCHAR(1000), @RKeyBuildField VARCHAR(1000), @Cnt SMALLINT 
	
	OPEN tempcursor2

	SELECT @FKeyBuildField = '', @RKeyBuildField = '', @Cnt = 0

	FETCH NEXT FROM tempcursor2 INTO @ConstraintID2 , @ForeignKeyColumn2 , @ReferenceKeyColumn2 , @KeySequenceNumber2
	WHILE (@@fetch_status <> -1)
	BEGIN
		SET @Cnt = @Cnt + 1
		SELECT @FKeyBuildField = @FKeyBuildField  + ISNULL(@ForeignKeyColumn2,'')+ 
			CASE 
				WHEN @ForeignKeyColumn2 IS NULL THEN '' 
			ELSE  
				CASE WHEN @KeySequenceNumber = @KeySequenceNumber2 THEN '' ELSE ',' END
			END

		SELECT @RKeyBuildField = @RKeyBuildField  + ISNULL(@ReferenceKeyColumn2,'')+ 
			CASE 
				WHEN @ReferenceKeyColumn2 IS NULL THEN '' 
			ELSE  
				CASE WHEN @KeySequenceNumber = @KeySequenceNumber2 THEN '' ELSE ',' END
			END
	
		INSERT INTO #Const
		(	
			ConstraintID , 
			FBuildField , 
			RBuildField, 
			CountField  
		)
		VALUES
		(
			@ConstraintID, 
			@FKeyBuildField,
			@RKeyBuildField,
			@Cnt
		)

		FETCH NEXT FROM tempcursor2 INTO @ConstraintID2 , @ForeignKeyColumn2 , @ReferenceKeyColumn2 , @KeySequenceNumber2
	END
	CLOSE tempcursor2
	DEALLOCATE tempcursor2

	FETCH NEXT FROM tempCursor INTO 
	@ConstraintName ,
	@ForeignKeyObject ,
	@ReferenceObject ,
	@ForeignKeyColumn ,
	@ReferenceKeyColumn,
	@ConstraintID , 
	@ForeignKeyID , 
	@ReferenceKeyID , 
	@KeySequenceNumber ,
	@ForeignKeyTable ,
	@ReferenceKeyTable 
END

CLOSE tempcursor
DEALLOCATE tempcursor

SELECT 'ALTER TABLE [' + FKTable + '] DROP CONSTRAINT ' + OBJECT_NAME(a.ConstraintID) AS DropKeys,
'ALTER TABLE [' + FKTable + '] WITH NOCHECK ADD CONSTRAINT ' + OBJECT_NAME(a.ConstraintID) + ' FOREIGN KEY(' + FBuildField + ') REFERENCES ' + RKTable + '(' + RBuildField+')' AS BuildKeys
,*
FROM #Const a
JOIN
(
	SELECT ConstraintID, MAX(countfield) AS maxcount
	FROM #Const
	GROUP BY ConstraintID
) b ON a.ConstraintID = b.ConstraintID  AND a.countfield = b.maxcount
JOIN
(
SELECT DISTINCT constraintid, OBJECT_NAME(foreignkeyid) AS FKTable, OBJECT_NAME(referencekeyid) AS RKTable FROM #fk
) c ON a.constraintid = c.constraintid

DROP TABLE #Const

Once you generate the DROP and CREATE scripts, save them to a file somewhere. You copy all of the DROP scripts and run them now, but make sure you have the CREATE script saved somewhere. You'll need to run it later once you've went through truncated your tables.