Ms Sql Server Technology Discussions

[fake_Bezawada]

superb thread i'll never touch dis no more spamming here

all the best

[deals2buy]

[b] Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query[/b]


Everybody knows about NOLOCK but not everyone knows about NOWAIT. They are different and they have an entire different purpose.

Here is the quick conversation between two people.

[b]SQL Guy:[/b] Pinal, in SQL Server when we have one query under transaction, when we have to read the data from the table we use NOLOCK, which gives us dirty data.

[b]Dave:[/b] Yes, that is correct.

[b]SQL Guy[/b][b]:[/b] In our application we do not want to wait for the query to execute, as the way the application we have built, we have to either return the results immediately or inform user that they should retry after a while as an application (database) is busy right now. Is there any other mechanism besides NOLOCK which we can use which inform us that the table from which we are trying to retrieve the data is locked under other transaction. This way, we can pass the same message to the user and they can re-attempt to fetch the data after a specific amount of the time.

[b]Dave[/b][b]:[/b] So, basically you do not want to use NOLOCK as it gives you dirty data and you do not want to wait also till the tables under under transactions is available. You rather prefer error instead or message that your query is not going to execute immediately.

[b]SQL Guy[/b][b]:[/b] Yes, absolutely correct.

[b]Dave[/b][b]:[/b] Use NoWait or SET SET LOCK_TIMEOUT.

I hope the above conversation also explains the real world scenario and business need of such feature.

NOWAIT is table hint and it instructs the database engine to return a message as soon as a lock is encountered on a table. Let us see an example.

First Create the following table in the database:

[CODE]
USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO
[/CODE]

Now open two different connections:

Connection 1: Run following query

[CODE]
BEGIN TRAN
DELETE FROM First
WHERE ID = 1
Connection 2: Run following query
BEGIN TRAN
SELECT ID, Col1
FROM First WITH(NOWAIT)
WHERE ID = 1
[/CODE]

As soon as you run above query in connection 2 it will give you following error:
[color=#ff0000]Msg 1222, Level 16, State 45, Line 2[/color]
[color=#ff0000]Lock request time out period exceeded.[/color]
[img]http://www.pinaldave.com/bimg/nowaittablehint.jpg[/img]

The reason is because we have used NOWAIT as a query hint. When this hint is encountered by SQL Server it will give an error 1222 back instead of waiting for transaction on another window to complete. As I said NOWAIT is very different than NOLOCK but very similar to SET SET LOCK_TIMEOUT. In future blog posts, we will see how SET SET LOCK_TIMEOUT works. The matter of fact SET SET LOCK_TIMEOUT is more flexible than NOWAIT and I often use it.

[deals2buy]

Learned a very interesting thing today

This might be very basic to you guys, but I came to know this only today

I am trying to create a table in SQLServer. The syntax is as follows:

[CODE]
CREATE TABLE [dbo].[TESTTABLE] (
[TEST1] [nvarchar(50)] ,
[TEST2] [int] ,
[TEST3] [varchar(MAX)]
) ON [PRIMARY]
[/CODE]


I am getting an error saying

[color=#ff0000]Cannot find data type varchar(MAX).[/color]


And the correct syntaxt for this would be :

[CODE]
CREATE TABLE [dbo].[TESTTABLE] (
[TEST1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS ,
[TEST2] [int] ,
[TEST3] [varchar](MAX)
) ON [PRIMARY]
[/CODE]

The Length Number should be given outside of the parenthesis "[]"

[deals2buy]

[b] Clustered Indexes[/b]


Indexes are fundamental to database design, and tell the developer using the database a great deal about the intentions of the designer. Unfortunately indexes are too often added as an afterthought when performance issues appear. Here at last is a simple series of articles that should bring any database professional rapidly 'up to speed' with them

This level begins by focusing on the third choice in the above list; searching the table. This in turn, will lead us to a discussion of clustered indexes.

The primary [i]AdventureWorks[/i] database table that we will be using during this level is the [i]SalesOrderDetail[/i] table. At 121,317 rows, it is large enough to illustrate some benefits of a having clustered index on a table. And, having two foreign keys, it is complex enough to illustrate some the design decisions you must make about your clustered indexes.
[b] Sample Database[/b]

Although we already discussed the sample database in Level 1, it bears repeating at this time. Throughout this Stairway we will use examples to illustrate concepts. These examples are based upon the Microsoft [i]AdventureWorks[/i] sample database. We focus on sales orders. Five tables will give us a good mix of transactional and non-transactional data; [i]Customer[/i], [i]SalesPerson[/i], [i]Product[/i], [i]SalesOrderHeader[/i], and [i]SalesOrderDetail[/i]. To keep things focused, we use a subset of the columns. Because [i]AdventureWorks[/i] is well normalized, sales person information is factored into three tables: [i]SalesPerson[/i], [i]Employee[/i] and [i]Contact[/i].

Throughout this Stairway we use the following two terms that refer to a single line on an order interchangeably: “line item” and “order detail”. The former is the more common business term; the latter appears within the name of an [i]AdventureWorks[/i] table.

The complete set of tables, and the relationships between them, is shown in Figure 1.

[center][b][img]http://www.sqlservercentral.com/Images/8456.gif[/img][/b][/center]

[center][b]Figure 1: The tables used in the examples in this Stairway[/b][/center][indent]
[b]Note:[/b]
All TSQL code shown in this Stairway level can be downloaded along with article.[/indent]
[b] Clustered Indexes[/b]

We start by asking the following question: how much work is required to find a row(s) in a table if a nonclustered index is not used? Does searching the table for the requested rows mean scanning every row in an unordered table? Or could SQL Server permanently sequence the rows of the table so that it could quickly access them by search key, just as it quickly accesses the entries of a nonclustered index by search key? The answer depends upon whether you instructed SQL Server to create a clustered index on the table or not.

Unlike nonclustered indexes, which are a separate object and occupy their own space, the clustered index and the table are one and the same. By creating a clustered index, you instruct SQL Server to sort the rows of the table into index key sequence and to maintain that sequence during future data modifications. Upcoming levels will look at the internal data structures that are generated to accomplish this. But for now, think of a clustered index as a sorted table. Given the index key value for a row, SQL Server can quickly access that row; and can proceed sequentially through the table from that row.

For demonstration purposes we create two copies of our example table, [i]SalesOrderDetail[/i]; one with no indexes and one with a clustered index. Regarding the index’s key columns, we make the same choice that designers of the [i]AdventureWorks[/i]database made: [i]SalesOrderID[/i] / [i]SalesOrderDetailID[/i]. The code in Listing 1 makes the copies of the [i]SalesOrderDetail[/i] table. We can rerun this code anytime we wish to start with a ‘clean slate’.

[CODE]
IF EXISTS (SELECT * FROM sys.tables 
WHERE OBJECT_ID = OBJECT_ID('dbo.SalesOrderDetail_index'))
DROP TABLE dbo.SalesOrderDetail_index;
GO
IF EXISTS (SELECT * FROM sys.tables 
WHERE OBJECT_ID = OBJECT_ID('dbo.SalesOrderDetail_noindex'))
DROP TABLE dbo.SalesOrderDetail_noindex;
GO


SELECT * INTO dbo.SalesOrderDetail_index FROM Sales.SalesOrderDetail;
SELECT * INTO dbo.SalesOrderDetail_noindex FROM Sales.SalesOrderDetail;
GO


CREATE CLUSTERED INDEX IX_SalesOrderDetail
ON dbo.SalesOrderDetail_index (SalesOrderID, SalesOrderDetailID)
GO
[/CODE]



[b]Listing 1: Create copies of the SalesOrderDetail table[/b]

So, assume the [i]SalesOrderDetail[/i] table looks like this before a clustered index is created:

SalesOrderID SalesOrderDetailID ProductID OrderQty UnitPrice
69389 102201 864 3 38.10
56658 59519 711 1 34.99
59044 70000 956 2 1430.442
48299 22652 853 4 44.994
50218 31427 854 8 44.994
53713 50716 711 1 34.99
50299 32777 739 1 744.2727
45321 6303 775 6 2024.994
72644 115325 873 1 2.29
48306 22705 824 4 141.615
69134 101554 876 1 120.00
48361 23556 760 3 469.794
53605 50098 888 1 602.346
48317 22901 722 1 183.9382
66430 93291 872 1 8.99
65281 90265 889 2 602.346
52248 43812 871 1 9.99
47978 20189 794 2 1308.9375

After creating the clustered index shown above, the resulting table / clustered index would look like this:

SalesOrderID SalesOrderDetailID ProductID OrderQty UnitPrice
43668 106 722 3 178.58
43668 107 708 1 20.19
43668 108 733 3 356.90
43668 109 763 3 419.46
43669 110 747 1 714.70
43670 111 710 1 5.70
43670 112 709 2 5.70
43670 113 773 2 2,039.99
43670 114 776 1 2,024.99
43671 115 753 1 2,146.96
43671 116 714 2 28.84
43671 117 756 1 874.79
43671 118 768 2 419.46
43671 119 732 2 356.90
43671 120 763 2 419.46
43671 121 755 2 874.79
43671 122 764 2 419.46
43671 123 716 1 28.84
43671 124 711 1 20.19
43671 125 708 1 20.19
43672 126 709 6 5.70
43672 127 776 2 2,024.99
43672 128 774 1 2,039.99
43673 129 754 1 874.79
43673 130 715 3 28.84
43673 131 729 1 183.94

As you look at the sample data shown above, you may notice that each [i]SalesOrderDetailID[/i] value is unique. Do not be confused; [i]SalesOrderDetailID[/i] is not the primary key of the table. The combination of [i]SalesOrderID[/i] / [i]SalesOrderDetailID[/i] is the primary key of the table; as well as the index key for the clustered index.
[b] Understanding the Basics of Clustered Indexes[/b]

The clustered index key can be comprised of any columns you chose; it does not have to be based on the primary key. In our example here, what is most important is that the left most column of the key is a foreign key, the [i]SalesOrderID[/i] value. Thus, all line items for a sales order appear consecutively within the [i]SalesOrderDetail[/i] table.

Keep in mind these additional points about SQL Server clustered indexes:[list]
[*]Because the entries of the clustered index are the rows of the table, there is no bookmark value in a clustered index entry. When SQL Server is already at a row, it does not need a piece of information that tells it where to find that row.
[*]A clustered index always covers the query. Since the index and the table are one and the same, every column of the table is in the index.
[*]Having a clustered index on a table does not impact your options for creating nonclustered indexes on that table.
[/list]
[b] Choosing the Clustered Index Key Column(s)[/b]

There can be, at most, one clustered index per table. The rows of a table can be in only one sequence. You need to decide what sequence, if any, would be best for each table; and, if possible, create the clustered index before the table becomes filled with data. When making this decision, keep in mind that sequencing not only means ordering, it also means grouping; as in grouping line items by sales order.
This is why the designers of the [i]AdventureWorks[/i]database chose [i]SalesOrderDetailID[/i] within [i]SalesOrderID[/i] as the sequence for the [i]SalesOrderDetail[/i] table; it is the natural sequence for line items.

For instance, if a user requests a line item of an order, they will usually request all the line items for that order. One look at a typical sales order form tells us that a printed copy of the order always includes all the line items. It is the nature of the sales order business to cluster line items by sales order. There may be an occasional request from the warehouse wanting to view line items by product rather than by sales order; but the majority of the requests; such as those from sales people, or customers, or the program that prints invoices, or a query that calculates the total value of each order; will need all the line items for any given sales order.

User requirements alone, however, do not determine what would be the best clustered index. Future levels in this series will cover the internals of indexes; because certain internal aspects of indexing will also influence your choice of clustered index columns.
[b] Heaps[/b]

If there is no clustered index on a table, the table is called a heap. Every table is either a heap or a clustered index. So, although we often state that every index falls into one of two types, clustered or nonclustered; it is equally important to note that every table falls into one of two types; it is a clustered index or it is a heap. Developers often say that a table “has” or “does not have” a clustered index, but it is more meaningful to say that the table “is” or “is not” a clustered index.

There is only one way for SQL Server to search a heap when looking for rows (excluding the use of nonclustered indexes), and this is to start at the very first row in the table and proceed through the table until all the rows have been read. Without a sequence, there is no search key and no way to quickly navigate to specific rows.
[b] Comparing a Clustered Index with a Heap[/b]

To evaluate the performance of a clustered index versus a heap, listing 1 makes two copies of the [i]SalesOrderDetail[/i]table. One copy is the heap version, On the other, we create the same clustered index that is on the original table ([i]SalesOrderID, SalesOrderDetailID[/i]). Neither table has any nonclustered indexes.

We will run the same three queries against each version of the table; one that retrieves a single row, one that retrieves all rows for a single order, and one that retrieves all rows for a single product. We present the SQL and the results of each execution in the tables shown below.
Our first query retrieves a single row and the execution details are shown in Table 1.
SQL SELECT *
FROM SalesOrderDetail
WHERE SalesOrderID = 43671
AND SalesOrderDetailID = 120 Heap (1 row(s) affected)
Table 'SalesOrderDetail_noindex'. Scan count 1, logical reads 1495. Clustered Index (1 row(s) affected)
Table 'SalesOrderDetail_noindex'. Scan count 1, logical reads 3. Impact of having the Clustered Index IO reduced from 1495 reads to 3 reads. Comments No surprise. Table scanning 121,317 rows to find just one is not very efficient.
[b]Table 1: Retrieving a single row[/b]


Our second query retrieves all rows for a single Sales Order, and you can see the execution details in Table 2.
SQL SELECT *
FROM SalesOrderDetail
WHERE SalesOrderID = 43671 Heap (11 row(s) affected)
Table 'SalesOrderDetail_noindex'. Scan count 1, logical reads 1495. Clustered Index (11 row(s) affected)
Table 'SalesOrderDetail_noindex'. Scan count 1, logical reads 3. Impact of having the Clustered Index IO reduced from 1495 reads to 3 reads. Comments Same statistics as the previous query. The heap still required a table scan, while the clustered index grouped the 11 detail rows of the requested order sufficiently close together so that the IO required to retrieve 11 rows was the same as the IO required to retrieve one row. An upcoming Level will explain in detail why no additional reads were required to retrieve the additional 10 rows.
[b]Table 2: Retrieving all rows for a single SalesOrder[/b]


And our third query retrieves all rows for a single Product, with the execution results as shown in Table 3.
SQL
SELECT *
FROM SalesOrderDetail
WHERE ProductID = 755
Heap
(228 row(s) affected)
Table 'SalesOrderDetail_noindex'. Scan count 1, logical reads 1495.
Clustered Index (228 row(s) affected)
Table 'SalesOrderDetail_index'. Scan count 1, logical reads 1513. Impact of having the Clustered Index IO slightly greater for the clustered index version; 1513 reads versus 1495 reads. Comments Without a nonclustered index on the ProductID column to help find the rows for a single Product, both versions had to be scanned. Because of the overhead of having a clustered index, the clustered index version is the slightly larger table; therefore scanning it required a few more reads than scanning the heap.
[b]Table 3: Retrieving all rows for a single Product[/b]

Our first two queries greatly benefitted from the presence of the clustered index; the third was approximately equal. Are there times when a clustered index is a detriment? The answer is yes, and it is mostly related to inserting, updating and deleting rows. Like so many other aspects of indexing encountered in these early Levels, it too is a subject that will be covered in more detail in a higher Level.

In general, the retrieval benefits outweigh the maintenance detriments; making a clustered index preferable to a heap. If you are creating tables in an Azure database, you have no choice; every table must be a clustered index.
[b] Conclusion[/b]

A clustered index is a sorted table whose sequence is specified by you when the index is created, and maintained by SQL Server. Any row in that table is quickly accessible given its key value. Any set of rows, in index key sequence, are also quickly accessible given the range of their keys.

There can be only one clustered index per table. The decision of which columns should be the clustered index key columns is the most important indexing decision that you will make for any table.

In our Level 4 we will shift our emphasis from the logical to the physical, introducing pages and extents, and examining the physical structure of indexes.
[b] Downloadable Code[/b]
[list]
[*][url="http://www.sqlservercentral.com/Images/8455.sql"]Clustered.SQL[/url]
[/list]

[deals2buy]

[b] We Loaded 1TB in 30 Minutes with SSIS, and So Can You[/b]




AFAIK, till date SSIS has a record breaking data load of all the ETL Tools

http://msdn.microsoft.com/en-us/library/dd537533.aspx

[bad__boy]

Karthikeya and Mtkr ee thread kee devulluu....gud gud keep going

[deals2buy]

[quote name='Kaarthikeya' timestamp='1359069120' post='1303169239']
You’re creating a database to contain information for a university web site: news, academic announcements, admissions, events, research, etc. Should you use the relational model or XML?
[/quote]


[b]Answer: [/b]Either one is appropriate

[b]Explanation: [/b]If your data is highly structured with known schema, the relational model is likely to work best for data storage. SQL Server provides the required functionality and tools you may need. On the other hand, if the structure is semi-structured or unstructured, or unknown, you have to give consideration to modeling such data.

XML is a good choice if you want a platform-independent model in order to ensure portability of the data by using structural and semantic markup. Additionally, it is an appropriate option if some of the following properties are satisfied:
•Your data is sparse or you do not know the structure of the data, or the structure of your data may change significantly in the future. •Your data represents containment hierarchy, instead of references among entities, and may be recursive. •Order is inherent in your data. •You want to query into the data or update parts of it, based on its structure.

Ref: [url="http://www.sqlservercentral.com/links/1427054/290859"]http://msdn.microsoft.com/en-us/library/bb522493(v=SQL.105).aspx[/url]

[deals2buy]

[b] Shrinking Database is Bad – Increases Fragmentation – Reduces Performance[/b]


Here are the quick steps of the example.[list]
[*]Create a test database
[*]Create two tables and populate with data
[*]Check the size of both the tables
[list]
[*]Size of database is very low
[/list][*]Check the Fragmentation of one table
[list]
[*]Fragmentation will be very low
[/list][*]Truncate another table
[*]Check the size of the table
[*]Check the fragmentation of the one table
[list]
[*]Fragmentation will be very low
[/list][*]SHRINK Database
[*]Check the size of the table
[*]Check the fragmentation of the one table
[list]
[*]Fragmentation will be very HIGH
[/list][*]REBUILD index on one table
[*]Check the size of the table
[list]
[*]Size of database is very HIGH
[/list][*]Check the fragmentation of the one table
[list]
[*]Fragmentation will be very low
[/list]
[/list]
Here is the script for the same.

[CODE]
USE MASTER
GO
CREATE DATABASE ShrinkIsBed
GO
USE ShrinkIsBed
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Create FirstTable
CREATE TABLE FirstTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Create Clustered Index on ID
CREATE CLUSTERED INDEX [IX_FirstTable_ID] ON FirstTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Create SecondTable
CREATE TABLE SecondTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Create Clustered Index on ID
CREATE CLUSTERED INDEX [IX_SecondTable_ID] ON SecondTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Insert One Hundred Thousand Records
INSERT INTO FirstTable (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Insert One Hundred Thousand Records
INSERT INTO SecondTable (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
[/CODE]

Let us check the table size and fragmentation.
[img]http://www.pinaldave.com/bimg/ShrinkFrag1.jpg[/img]

Now let us TRUNCATE the table and check the size and Fragmentation.

[CODE]
-- TRUNCATE and SHRINKDB
TRUNCATE TABLE SecondTable
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Shrink the Database
DBCC SHRINKDATABASE (ShrinkIsBed);
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/ShrinkFrag2.jpg[/img]

You can clearly see that after TRUNCATE, the size of the database is not reduced and it is still the same as before TRUNCATE operation. After the Shrinking database operation, we were able to reduce the size of the database. If you notice the fragmentation, it is considerably high.

The major problem with the Shrink operation is that it increases fragmentation of the database to very high value. Higher fragmentation reduces the performance of the database as reading from that particular table becomes very expensive.
One of the ways to reduce the fragmentation is to rebuild index on the database. Let us rebuild the index and observe fragmentation and database size.

[CODE]
-- Rebuild Index on FirstTable
ALTER INDEX IX_SecondTable_ID ON SecondTable REBUILD
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/ShrinkFrag3.jpg[/img]
You can notice that after rebuilding, Fragmentation reduces to a very low value (almost same to original value); however the database size increases way higher than the original. Before rebuilding, the size of the database was 5 MB, and after rebuilding, it is around 20 MB. Regular rebuilding the index is rebuild in the same user database where the index is placed. This usually increases the size of the database.

[b][i]Look at irony of the Shrinking database. One person shrinks the database to gain space (thinking it will help performance), which leads to increase in fragmentation (reducing performance). To reduce the fragmentation, one rebuilds index, which leads to size of the database to increase way more than the original size of the database (before shrinking). Well, by Shrinking, one did not gain what he was looking for usually.[/i][/b]

Rebuild indexing is not the best suggestion as that will create database grow again.

Let us run following script where we Shrink the database and REORGANIZE.

[CODE]
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
-- Shrink the Database
DBCC SHRINKDATABASE (ShrinkIsBed);
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
-- Rebuild Index on FirstTable
ALTER INDEX IX_SecondTable_ID ON SecondTable REORGANIZE
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/ShrinkFrag4.jpg[/img]

You can see that REORGANIZE does not increase the size of the database or remove the fragmentation.

Again, I no way suggest that REORGANIZE is the solution over here. This is purely observation using demo.

Following script will clean up the database

[CODE]
-- Clean up
USE MASTER
GO
ALTER DATABASE ShrinkIsBed
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE ShrinkIsBed
GO
[/CODE]

There are few valid cases of the Shrinking database as well, but that is not covered in this blog post. We will cover that area some other time in future. Additionally, one can rebuild index in the tempdb as well, and we will also talk about the same in future.

[deals2buy]

[size=6][b]SQL Server Performance Tuning[/b][/size]

SQL Server Performance Tuning and health check is a very challenging subject that requires expertise in Database Administration and Database Development. Here are few pointers how one can keep their SQL Server Performance Optimal.

Here is the quick list of the pointers which one should consider for performance tuning.

[b][b]Server/Instance Level Configuration Check[/b][/b]

Review all the SQL Server/Instance Level settings of the server and tune it based on system workload.


[b][b]I/O distribution Analysis[/b][/b]

Analyze the I/O of your system and decide the best distribution for the I/O load. Perform object level analysis and do performance tuning at table level. Reduce I/O performance bottlenecks and suggest optimal setting for read and write database. This is especially critical for databases that need to sustain heavy updates during peak usage hours.


[b][b]SQL Server Resource Wait Stats Analysis[/b][/b]

Wait Stat Analysis is very crucial for optimizing databases, but is often overlooked due to lack of understanding. Perform advanced resource wait statistics analysis to proactively reduce performance bottleneck.


[b][b]Index Analysis[/b][/b]

Indexes are considered valuable for performance improvements. Analyze all your indexes to identify non-performing indexes and missing indexes that can improve performance.


[b][b]TempDB Space Review[/b][/b]

Review the size and usage of your TempDB database.


[b][b]Database Files (MDF, NDF) and Log File Inspection[/b][/b]

Review all the files and filegroups of each of your databases and analysis them to identify any object or files that are causing bottlenecks.
[b] [/b]

[b][b]Fragmentations and Defragmentations[/b][/b]

Identify the optimal settings of your database files and tables to reduce fragmentation and reduce them.
[b] [/b]

[b][b]Backup and Recovery health Check[/b][/b]

Review all backup & recovery settings and procedures of your databases and understand the functions of the respective databases.
[b] [/b]

[b][b]Log Reviews (Windows Event Logs, SQL Server Error Logs and Agent Logs)[/b][/b]

Logs reveal many hidden facts. Identity the critical errors and resolve them.
[b] [/b]

[b][b]Hardware Review[/b][/b]

Review the hardware and and verify that it positively impact the performance or scalability of the database.
[b] [/b]

[b][b]DBCC Best Practices Implementations[/b][/b]

There are few DBCC commands to be avoided and few very crucial for system. Understand the usage of DBCC FREEPROCCACHE, DBCC SRHINKDATABASE, DBCC SHRINKFILE, DBCC DROPCLEANBUFFER, DBCC REINDEX, as well as the usage of few system stored procedures like SP_UPDATESTATS. If you are currently using any of the above mentioned and a few other DBCC maintenance task commands carefully review their usage.
[b] [/b]

[b][b]Deadlock Detection and Resolutions Hands-On[/b][/b]

Detecting deadlock is not very difficult, but to learn the tips and trick to resolve them requires an understanding of the issue and some experience. Understand the locking mechanism and resolve them.

[deals2buy]

[b] A Funny Cartoon on Index[/b]


[img]http://www.pinaldave.com/bimg/indexcartoon.jpg[/img]
Any way, If you think Index solves all of your performance problem I think it is not true. There are many other reason one has to consider along with Indexes. For example I consider following various topic one need to understand for performance tuning.[list]
[*]žLogical Query Processing
[*]žEfficient Join Techniques
[*]žQuery Tuning Considerations
[*]žAvoiding Common Performance Tuning Issues
[*]Statistics and Best Practices
[*]žTempDB Tuning
[*]žHardware Planning
[*]žUnderstanding Query Processor
[*]žUsing SQL Server 2005 and 2008 Updated Feature Sets
[*]žCPU, Memory, I/O Bottleneck
[*]Index Tuning (of course)
[*]žMany more…
[/list]

[deals2buy]

[b] A Quick Note on DB_ID() and DB_NAME() – Get Current Database ID – Get Current Database Name[/b]

Quite often a simple things makes experienced DBA to look for simple thing. Here are few things which I used to get confused couple of years ago. Now I know it well and have no issue but recently I see one of the DBA getting confused when looking at the DBID from one of the DMV and not able to related that directly to Database Name.

[CODE]
-- Get Current DatabaseID
SELECT DB_ID() DatabaseID;
-- Get Current DatabaseName
SELECT DB_NAME() DatabaseName;
-- Get DatabaseName from DatabaseID
SELECT DB_NAME(4) DatabaseID;
-- Get DatabaseID from DatabaseName
SELECT DB_ID('tempdb') DatabaseName;
-- Get all DatabaseName and DBID
SELECT name,database_id
FROM sys.databases;
[/CODE]

[mtkr]

[quote name='bad__boy' timestamp='1359155751' post='1303177956']
Karthikeya and Mtkr ee thread kee devulluu....gud gud keep going
[/quote]
naadi em led maayaa... pilla beep ganni...
raaju mahaa raaajuuuuu deals2buy maayaa...

[deals2buy]

[b] Find Busiest Database[/b]

How to find which is the busiest database in any SQL Server Instance. What it means is, which database was doing lots of read and write operation.

To find the answer to this question I decided to look into the DMV which contains all the details of the executed query. From the DMV sys.dm_exec_query_stats I found three most important columns to determine busiest database.

[img]http://www.pinaldave.com/bimg/tlrw.jpg[/img]

DMV sys.dm_exec_query_stats contained columns total_logical_reads, total_logical_writes, sql_handle. Column sql_handle can help to to determine the original query by CROSS JOINing DMF sys.dm_exec_sql_text. From DMF sys.dm_exec_sql_text Database ID and from Database ID can be figured out very quickly.

[CODE]
SELECT SUM(deqs.total_logical_reads) TotalPageReads,
SUM(deqs.total_logical_writes) TotalPageWrites,
CASE
WHEN DB_NAME(dest.dbid) IS NULL THEN 'AdhocSQL'
ELSE DB_NAME(dest.dbid) END Databasename
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
GROUP BY DB_NAME(dest.dbid)
[/CODE]

[img]http://www.pinaldave.com/bimg/tlrw1.jpg[/img]

[deals2buy]

[b] SSMS Query Command(s) completed successfully without ANY Results[/b]


[img]http://www.pinaldave.com/bimg/setparseonly1.jpg[/img]

[img]http://www.pinaldave.com/bimg/setparseonly2.jpg[/img]

[img]http://www.pinaldave.com/bimg/setparseonly3.jpg[/img]

We could have used T-SQL to turn this option off and also the [b][i]SET PARSEONLY OFF [/i][/b]option[b][i].[/i][/b]

[img]http://www.pinaldave.com/bimg/setparseonly4.jpg[/img]

[deals2buy]

[b] SQL SERVER – Query Analyzer Shortcuts[/b]

[b][url="http://www.pinaldave.com/download/queryanalyzershortcuts.pdf"]Download Query Analyzer Shortcuts (PDF)[/url][/b]