Conncet SQL
Your Daily Dose of SQL Server
SQL Server: How to Analyze Ad Hoc Queries Workload
0355 days
by lionofdezert
in Daily Dose
Selecting an optimized execution plan, is a time consuming process, which query optimizer do for us, when ever a query is executed. If query is too complex then optimizer have to do a lot of work. And once the plan is found, it is cached in memory first, so it can be used on next execution of same query.
Most of the time, cached execution plan that were created for Ad Hoc queries can not be reused and if percentage of such cached plans that are used only once is comparatively high then it must be considered as an alarming situation for overall database performance.
Following query can be helpful for analyzing cached execution plans.
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
Last Column “Total Plans – USE Count 1” shows total number cached plans in given category that are used only once.
SQL Server: Create/Drop Scripts for All Existing Foreign Keys
0356 days
by lionofdezert
in Daily Dose
Today, when I need a script to get create and drop scripts of all existing foreign keys on a specific table (or in a whole database), I searched my query bank and find my required script quickly but on opening it I found that I was written for SQL Server 2000, time when there was no concept of schema, so I have made some changes so I can use it for SQL Server 2005 or 2008.
Create Foreign Keys:
SELECT ‘ALTER TABLE ‘ + SCHEMA_NAME(F.schema_id) + ‘.’
+ OBJECT_NAME(F.parent_object_id) + ‘ ADD CONSTRAINT ‘ + F.name
+ ‘ FOREIGN KEY ‘ + ‘(‘ + COL_NAME(FC.parent_object_id,
FC.parent_column_id) + ‘)’
+ ‘ REFERENCES ‘ + SCHEMA_NAME(RefObj.schema_id) + ‘.’
+ OBJECT_NAME(F.referenced_object_id) + ‘ (‘
+ COL_NAME(FC.referenced_object_id, FC.referenced_column_id) + ‘)’
FROM SYS.FOREIGN_KEYS AS F
INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id
INNER JOIN sys.objects RefObj ON RefObj.object_id = f.referenced_object_id
–WHERE OBJECT_NAME(F.PARENT_OBJECT_ID) = ‘YourObjectName’
Drop Foreign Keys
SELECT ‘ALTER TABLE ‘ + SCHEMA_NAME(F.schema_id) + ‘.’
+ OBJECT_NAME(F.parent_object_id) + ‘ DROP CONSTRAINT ‘ + F.name
FROM SYS.FOREIGN_KEYS AS F
INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id
–WHERE OBJECT_NAME(F.PARENT_OBJECT_ID) = ‘YourObjectName’
SQL Server: Shortcuts for TSQL Code in SSMS
0357 days
by lionofdezert
in Daily Dose
For a developer or DBA it’s common to start their day with “SELECT * FROM” and in a daily routine work we type same lines of script many times. If you are a lazy developer or DBA like me then sometime it feels boring to type same code again and again. Intellisence in SQL Server 2008, saved lot of time but still it lacks few features, which other third party tools were offering before SQL Server 2008. Through such tools like SQL PROMPT, we have ability to type shortcuts in editor which after pressing Enter or Tab turn into a predefined code block. Like I will just type * and it will convert it to “SELECT * FROM”.
If you don’t like to purchase these tools but still want to make your life easy then you need SSMS Tools by Mladen Prajdić, totally free and you can download from here. Beside other good tools it has an option of SQL Snippets. Although it already have a good list of shortcuts but still you can add of your choice.
It has shortcuts like:
SSC = SELECT COUNT(*) FROM
SSF = SELECT * FROM
UPD = UPDATE <>
SET <>
FROM <>
SQL Server: Which Performs Better, IN or EXISTS
0359 days
by lionofdezert
in Daily Dose
It’s true that to solve a problem in TSQL you have many choices, and adopting any of these techniques you can get your desired results. Today we will discuss two very commonly used IN and EXISTS clause to filter query result set based on records from a sub-query. As compared to EXISTS, IN is more commonly used, but which one is more efficient? That’s what we are looking for, today.
IN and EXISTS perform in a same way if we use them with single column search. Like as follow:
USE AdventureWorks
GO
–IN
SELECT * FROM Production.Product pr
WHERE ProductID IN
(SELECT ProductID FROM Purchasing.ProductVendor)
–EXISTS
SELECT * FROM Production.Product pr
WHERE EXISTS
(SELECT 1 FROM Purchasing.ProductVendor
WHERE ProductID = pr.ProductID)
WHERE ProductID = pr.ProductID)
Logical reads and query elapsed time is also same for both queries.
In situations where you need to filter records based on more than one columns existence in sub-query, you will find EXISTS much better in performance. To observe this, let’s create two temporary tables.
CREATE TABLE #Cars (Make VARCHAR(50), Color VARCHAR(30), Seats INT)
CREATE TABLE #CarIssuance (Make VARCHAR(50),Color VARCHAR(30),IssuanceDate DATETIME)
GO
INSERT INTO #Cars
SELECT ‘Honda’,‘Black’,2 union all
SELECT ‘Honda’,‘White’,2 union all
SELECT ‘Toyota‘,‘Black’,4 union all
SELECT ‘Toyota‘,‘Silver’,4 union all
SELECT ‘BMW’, ‘Red’,2
GO
INSERT INTO #CarIssuance
SELECT ‘Honda’,‘Black’,’2011-05-20′ union all
SELECT ‘BMW’,‘Red’,’2011-05-03′ union all
SELECT ‘Toyota‘,‘Black’,’2011-05-03′
If we need to get all records from #Cars table, where records exists in #CarIssuance table on basis of “make” and “color” columns. Let’s first try traditional IN clause.
SELECT * FROM #Cars
WHERE Color IN
(SELECT Color FROM #CarIssuance)
AND Make IN
(SELECT Make FROM #CarIssuance)
Table ‘#CarIssuance Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0
Table ‘#Cars Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 40 ms.
Now execute same query with EXISTS option
SELECT * FROM #Cars Cr
WHERE
EXISTS
(SELECT 1 FROM #CarIssuance CI
WHERE Make = Cr.Make
AND Color = Cr.Color)
Table ‘#CarIssuance Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0
Table ‘#Cars Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Exists performed much better (We got result in 1ms instead of 40ms) just because we have used single sub-query but in case of “IN”, we have used multiple sub-queries. So if somehow, we change our first query of “IN” clause so that we can use only one sub-query, performance will be same as to EXISTS. But code will be bit mixed up and in real life sometime even its not possible.
SELECT * FROM #Cars Cr
WHERE Color IN
(SELECT Color FROM #CarIssuance WHERE Make = Cr.Make)
Summary: Exists performs much better when used for more then one column filter from a sub-query but as a good programming practice EXISTS must be preferred even when handling with single column filter.
SQL Server:A Quick Intorduction to tempdb
0360 days
by lionofdezert
in Daily Dose
TempDB is one of system databases to manage the SQL Server instance. Following is a quick introduction to tempdb:
- SQL Server uses tempdb to store internal objects such as the intermediate results of a query
- tempdb does not persist after SQL Server shuts down
- Each time SQL Server restarts, tempdb is copied from the model database
- Only one file group in tempdb is allowed for data and one file group for logs
- Auto grow is temporary for tempdb (unlike other types of databases). It is reset when SQL Server restarts
- . In a user database, transactions have the ACID attributes: atomicity, concurrency, isolation, and durability. In tempdb, transactions lose the durability attribute
- Auto shrink is not allowed for tempdb
- The database CHECKSUM option cannot be enabled.
- A database snapshot cannot be created on tempdb.
- DBCC CHECKALLOC and DBCC CHECKCATALOG are not supported.
- Only offline checking for DBCC CHECKTABLE is performed. This means that a TAB-S lock is needed. There are internal consistency checks that occur when tempdb is in use. If these checks fail, the user connection is broken and the tempdb space used by the connection is freed.
Which objects occupy tempdb space
The following types of objects can occupy tempdb space:
- Internal objects
- Version stores
- User objects
Internal objects are used:
- To store intermediate runs for sort.
- To store intermediate results for hash joins and hash aggregates.
- To store XML variables or other large object (LOB) data type variables. The LOB data type includes all of the large object types: text, image, ntext, varchar(max), varbinary(max), and all others.
- By queries that need a spool to store intermediate results.
- By keyset cursors to store the keys.
- By static cursors to store a query result.
- By Service Broker to store messages in transit.
- By INSTEAD OF triggers to store data for internal processing.
Must remember that:
- Updates to internal objects do not generate log records
- Each internal object occupies at least nine pages (one IAM page and eight data pages) in tempdb
Version store
- Version stores are used to store row versions generated by transactions for features such as snapshot isolation, triggers, MARS (multiple active result sets), and online index build
- Inserts into the version store do not generate log records
- INSTEAD OF triggers do not generate versions
User objects
- Operations on user objects in tempdb are mostly logged.
- Bulk copy program (BCP), bulk insert, SELECT INTO, and index rebuild operations are bulk logged. This is exactly the same as in other databases with the recovery model set to simple
tempdb Space Requirements
It is difficult to estimate the tempdb space requirement for an application.
But it is recommend that you always have a safety factor of about 20% more space
SQL Server: Short Code Doesn’t Mean Smart Code
0372 days
by lionofdezert
in Daily Dose
No one is unaware of WHERE clause. Everyone knows that we can filter output records by using WHERE clause but recently I found my team member stuck when he need a Conditional WHERE clause (filter for a filter). Let’s try to figure out the problem and its solution with a simple scenario.
SQL Server: Conditional WHERE clause (Filter for a Filter)
1376 days
by lionofdezert
in Daily Dose
No one is unaware of WHERE clause. Everyone knows that we can filter output records by using WHERE clause but recently I found my team member stuck when he need a Conditional WHERE clause (filter for a filter). Let’s try to figure out the problem and its solution with a simple scenario.