SQL SERVER – Size of Index Table for Each Index – Solution 2

Earlier I had ran puzzle where I asked question regarding size of index table for each index in database over here SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table. I had received good amount answers and I had blogged about that here SQL SERVER – Size of Index Table for Each Index – Solution. As a comment to that blog I have received another very interesting comment and that provides near accurate answers to original question. Many thanks to Rama Mathanmohan for providing wonderful solution.

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

Let me know if you have any better script for the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Note: Please read the complete post before taking any actions.

This blog post would discuss SHRINKFILE and TRUNCATE Log File. The script mentioned in the email received from reader contains the following questionable code:

“Hi Pinal,

If you could remember, I and my manager met you at TechEd in Bangalore.

We just upgraded to SQL Server 2008. One of our jobs failed as it was using the following code.

The error was:

Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

The code was:

DBCC SHRINKFILE(TestDBLog, 1)
BACKUP LOG TestDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TestDBLog, 1)
GO

I have modified that code to subsequent code and it works fine. But, are there other suggestions you have at the moment?

USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

Configuration of our server and system is as follows:

[Removed not relevant data]”

An email like this that suddenly pops out in early morning is alarming email. Because I am a dead, busy mind, so I had only one min to reply. I wrote down quickly the following note. (As I said, it was a single-minute email so it is not completely accurate). Here is that quick email shared with all of you.

“Hi Mr. DBA [removed the name]

Thanks for your email. I suggest you stop this practice. There are many issues included here, but I would list two major issues:

1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.

2) Shrinking database file or database adds fragmentation.

There are a lot of things you can do. First, start taking proper log backup using following command instead of truncating them and losing them frequently.

BACKUP LOG [TestDb] TO  DISK = N'C:\Backup\TestDb.bak'
GO

Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.

There are so many things to add here, but you can call me on my [phone number]. Before you call me, I suggest for accuracy you read Paul Randel‘s two posts here and here and Brent Ozar‘s Post here.

Kind Regards,
Pinal Dave”

I guess this post is very much clear to you. Please leave your comments here. As mentioned, this is a very huge subject; I have just touched a tip of the ice-berg and have tried to point to authentic knowledge.

Update: Small typo correction and small detail corrected based on feedback.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – GUID vs INT – Your Opinion

I think the title is clear what I am going to write in your post.

This is age old problem and I want to compile the list stating advantages and disadvantages of using GUID and INT as a Primary Key or Clustered Index or Both (the usual case).

Let me start a list by suggesting one advantage and one disadvantage in each case.

INT

Advantage:

  1. Numeric values (and specifically integers) are better for performance when used in joins, indexes and conditions.
  2. Numeric values are easier to understand for application users if they are displayed.

Disadvantage:

  1. If your table is large, it is quite possible it will run out of it and after some numeric value there will be no additional identity to use.

GUID

Advantage:

  1. Unique across the server.

Disadvantage:

  1. String values are not as optimal as integer values for performance when used in joins, indexes and conditions.
  2. More storage space is required than INT.

Please note that I am looking to create list of all the generic comparisons. There can be special cases where the stated information is incorrect, feel free to comment on the same.

Please leave your opinion and advice in comment section. I will combine a final list and update this blog after a week. By listing your name in post, I will also give due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Attach mdf file without ldf file in Database

Background Story:
One of my friends recently called up and asked me if I had spare time to look at his database and give him a performance tuning advice. Because I had some free time to help him out, I said yes. I asked him to send me the details of his database structure and sample data. He said that since his database is in a very early stage and is small as of the moment, so he told me that he would like me to have a complete database. My response to him was “Sure! In that case, take a backup of the database and send it to me. I will restore it into my computer and play with it.”

He did send me his database; however, his method made me write this quick note here. Instead of taking a full backup of the database and sending it to me, he sent me only the .mdf (primary database file). In fact, I asked for a complete backup (I wanted to review file groups, files, as well as few other details).  Upon calling my friend,  I found that he was not available. Now,  he left me with only a .mdf file. As I had some extra time, I decided to checkout his database structure and get back to him regarding the full backup, whenever I can get in touch with him again.

Technical Talk:
If the database is shutdown gracefully and there was no abrupt shutdown (power outrages, pulling plugs to machines, machine crashes or any other reasons), it is possible (there’s no guarantee) to attach .mdf file only to the server. Please note that there can be many more reasons for a database that is not getting attached or restored. In my case, the database had a clean shutdown and there were no complex issues. I was able to recreate a transaction log file and attached the received .mdf file.

There are multiple ways of doing this. I am listing all of them here. Before using any of them, please consult the Domain Expert in your company or industry. Also, never attempt this on live/production server without the presence of a Disaster Recovery expert.

USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO
-- Method 2:
CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO

Method 2: If one or more log files are missing, they are recreated again.

There is one more method which I am demonstrating here but I have not used myself before. According to Book Online, it will work only if there is one log file that is missing. If there are more than one log files involved, all of them are required to undergo the same procedure.

-- Method 3:
CREATE DATABASE TestDb ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO

Please read the Book Online in depth and consult DR experts before working on the production server. In my case, the above syntax just worked fine as the database was clean when it was detached. Feel free to write your opinions and experiences for it will help the IT community to learn more from your suggestions and skills.

PS: Do not try this on production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Retrieve and Explore Database Backup without Restoring Database – Idera virtual database

I recently downloaded Idera’s SQL virtual database, and tested it. There are a few things about this tool which caught my attention.

My Scenario

It is quite common in real life that sometimes observing or retrieving older data is necessary; however, it had changed as time passed by. The full database backup was 40 GB in size, and, to restore it on our production server, it usually takes around 16 to 22 minutes, depending on the load server that is usually present. This range in time varies from one server to another as per the configuration of the computer. Some other issues we used to have are the following:

  1. When we try to restore a large 40-GB database, we needed at least that much space on our production server.
  2. Once in a while, we even had to make changes in the restored database, and use the said changed and restored database for our purpose, making it more time-consuming.

My Solution

I have heard a lot about the Idera’s SQL virtual database tool.. Well, right after we started to test this tool, we found out that it really delivers what it promises. Using this software was very easy and we were able to restore our database from backup in less than 2 minutes, sparing us from the usual longer time of 16–22 minutes. The needful was finished in a total of 10 minutes. Another interesting observation is that there is no need to have an additional space for restoring the database. For complete database restoration, the single additional MB on the drive is not required anymore. We can use the database in the same way as our regular database, and there is no need for any additional configuration and setup.

Let us look at the most relevant points of this product based on my initial experience:

  • Quick restoration of the database backup
  • No additional space required for database restoration
  • virtual database has no physical .MDF or .LDF
  • The database which is restored is, in fact, the backup file converted in the virtual database. DDL and DML queries can be executed against this virtually restored database.
  • Regular backup operation can be implemented against virtual database, creating a physical .bak file that can be used for future use.
  • There was no observed degradation in performance on the original database as well the restored virtual database.
  • Additional T-SQL queries can be let off on the virtual database.

Well, this summarizes my quick review. And, as I was saying, I am very impressed with the product and I plan to explore it more. There are many features that I have noticed in this tool, which I think can be very useful if properly understood.

I had taken a few screenshots using my demo database afterwards. Let us see what other things this tool can do besides the mentioned activities. I am surprised with its performance so I want to know how exactly this feature works, specifically in the matter of why it does not create any additional files and yet, it still allows update on the virtually restored database. I guess I will have to send an e-mail to the developers of Idera and try to figure this out from them.

I think this tool is very useful, and it delivers a high level of performance way more than what I expected. Soon, I will write a review for additional uses of SQL virtual database.. If you are using SQL virtual database in your production environment, I am eager to learn more about it and your experience while using it.

The ‘Virtual’ Part of virtual database

When I set out to test this software, I thought virtual database had something to do with Hyper-V or visualization. In fact, the virtual database is a kind of database which shows up in your SQL Server Management Studio without actually restoring or even creating it. This tool creates a database in SSMS from the backup of the same database. The backup, however, works virtually the same way as original database.

Potential Usage of virtual database:

As soon as I described this tool to my teammate, I think his very first reaction was, “hey, if we have this then there is no need for log shipping.” I find his comment very interesting as log shipping is something where logs are moved to another server. In fact, there are no updates on the database from log; I would rather compare it with Snapshot Replication. In fact, whatever we use, snapshot replicated database can be similarly used and configured with virtual database. I totally believe that we can use it for reporting purpose. In fact, after this database was configured, I think the uses of this tool are unlimited. I will have to spend some more time studying it and will get back to you.

Click on images to see larger images.

virtual database Console
Harddrive Space before virtual database Setup
Attach Full Backup Screen
Backup on Harddrive
Attach Full Backup Screen with Settings
virtual database Setup – less than 60 sec
virtual database Setup – Online
Harddrive Space after virtual database Setup
Point in Time Recovery Option – Timeline View
virtual database Summary
No Performance Difference between Regular DB vs Virtual DB

Please note that all SQL Server MVP gets free license of this software.

Reference: Pinal Dave (http://blog.SQLAuthority.com), Idera (virtual database)

SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot

Snapshot database is one of the most interesting concepts that I have used at some places recently.

Here is a quick definition of the subject from Book On Line:

A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner.

If you do not know how Snapshot database work, here is a quick note on the subject. However, please refer to the official description on Book-on-Line for accuracy. Snapshot database is a read-only database created from an original database called the “source database”. This database operates at page level. When Snapshot database is created, it is produced on sparse files; in fact, it does not occupy any space (or occupies very little space) in the Operating System. When any data page is modified in the source database, that data page is copied to Snapshot database, making the sparse file size increases. When an unmodified data page is read in the Snapshot database, it actually reads the pages of the original database. In other words, the changes that happen in the source database are reflected in the Snapshot database.

Let us see a simple example of Snapshot. In the following exercise, we will do a few operations. Please note that this script is for demo purposes only- there are a few considerations of CPU, DISK I/O and memory, which will be discussed in the future posts.

  • Create Snapshot
  • Delete Data from Original DB
  • Restore Data from Snapshot

First, let us create the first Snapshot database and observe the sparse file details.

USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO

Now let us see the resultset for the same.

Now let us do delete something from the Original DB and check the same details we checked before.

-- Delete from Regular Database
DELETE FROM RegularDB.dbo.FirstTable;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO

When we check the details of sparse file created by Snapshot database, we will find some interesting details. The details of Regular DB remain the same.

It clearly shows that when we delete data from Regular/Source DB, it copies the data pages to Snapshot database. This is the reason why the size of the snapshot DB is increased.

Now let us take this small exercise to  the next level and restore our deleted data from Snapshot DB to Original Source DB.

-- Restore Data from Snapshot Database
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Clean up
DROP DATABASE [SnapshotDB];
DROP DATABASE [RegularDB];
GO

Now let us check the details of the select statement and we can see that we are successful able to restore the database from Snapshot Database.

We can clearly see that this is a very useful feature in case you would encounter a good business that needs it.

I would like to request the readers to suggest more details if they are using this feature in their business. Also, let me know if you think it can be potentially used to achieve any tasks.

Complete Script of the afore- mentioned operation for easy reference is as follows:

USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Delete from Regular Database
DELETE FROM RegularDB.dbo.FirstTable;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Restore Data from Snapshot Database
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Clean up
DROP DATABASE [SnapshotDB];
DROP DATABASE [RegularDB];
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Data and Page Compressions – Data Storage and IO Improvement

The performance of SQL Server is primarily decided by the disk I/O efficiency. Improving I/O definitely improves the performance. SQL Server 2008 introduced Data and Backup compression features to improve the disk I/O. Here, I will explain Data compression.

Data compression implies the reduction in the disk space reserved by data. Therefore, data compression can be configured for a table, clustered index, non-clustered index, indexed view or a partition of table or index.

Data compression is implemented at two levels: ROW and PAGE. Even page compression automatically implements row compression. Tables and indexes can be compressed when they are created by using the CREATE TABLE and CREATE INDEX statements. To change the compression state of a table, index, or partition, use the ALTER TABLE.. REBUILD WITH or ALTER INDEX.. REBUILD WITH statements. When compression state of a heap is changed, all non-clustered index are rebuilt (not with the compression type of table but there own).

Let me tell you what happens during the compression of data. In Row compression, the following four methods are used to remove unused space:

  1. The metadata overhead of the record is reduced.
  2. All numeric (for example integer, decimal, and float) and numeric-based (for example datetime and money) data type values are converted into variable length values. By default, the values of these data types are stored as fixed length like integer in 4 bytes datetime in 8 bytes, but after compression all unconsumed space is reclaimed. For example, a value 100 is stored in an integer-type column. We know an integer value between 0 and 255 can be stored in 1 byte. However, it reserves 4 bytes (integer type takes 4 bytes) on disk. Here, after compression, 3 bytes are reclaimed.
  3. CHAR and NCHAR type values are stored in variable length format. After compression, no blank character is stored with this type of data. For example, a value “DBMS” is stored in CHAR(10) type column. By default, this value will reserve 10 bytes on disk, but after compression, it will reserve only 4 bytes.
  4. NULL and 0 values across all data types are optimized and take no bytes.

Page compression is implemented through the following three methods:

  1. Row compression: as discussed above
  2. Prefix compression: In each page, for each column, a common value from all rows is identified and stored in a row below the header. After that, from all the rows, that common value is replaced with the reference of header row.
  3. Dictionary compression: Dictionary compression is same concept as that of Prefix compression implemented the second time. In Prefix compression, a separate common value is identified for each column, but in Dictionary compression, common values are identified from all columns in that page and stored in second row below the header. Then, these common values are replaced with the reference of values in new row.

Below is an example. Even it is not a good practical scenario, but sufficiently explains the compression functionality.

Review your database for performance and implement this strategies. Let us run the following different row level and page level compression and observe the size of the table once compression is in place.

USE tempdb
GO
CREATE TABLE TestCompression (col1 INT, col2 CHAR(50))
GO
INSERT INTO TestCompression VALUES (10, 'compression testing')
GO 5000
-- Original
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = ROW
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = PAGE
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = NONE
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = NONE);
GO
EXEC sp_spaceused TestCompression
GO

Do you use this compression on your production server? If yes, it will be interesting if you share your feedback with us over here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)