Query to show Performance Counters that SQL Server exposes to Windows OS

T-SQL query that can quickly show some valuable performance counters from SQL Server. The values in this system view are actual, they get refreshed many times a second.

SELECT *

FROM master.sys.dm_os_performance_counters

WHERE

instance_name = 'MyDbName'

OR OBJECT_NAME = 'SQLServer:General Statistics'

OR counter_name = 'Batch Requests/sec'

Troubleshooting: if this query returns nothing then you may have Performance Counters disabled. Run this to verify, it should return at least one row : SELECT COUNT(*) from sys.dm_os_performance_counters;

SQL Server, query performance and Execution Plan panel

Noticed one interesting thing with sql management studio.

Was trying to pinpoint some performance issues with a query which had WHILE loop. I switched on the execution plan to analyse the query. It was strange – like if performance was degrading with each new iteration. After several rounds of optimizations there was no luck, same performance degradation.

I created very simple test shown below (Example 1). It does nothing but counts the rows in sys.objects table 500 times so it returns the same result each time. There are 1570 rows in  MyDatabase.sys.objects on my machine

Still no luck – average execution times were rising, total time was more than a minute

Switched off the execution plan and it complete in less than a second!

The conclusion is that Execution Plan panel should be used carefully when your queries have WHILE loops – use Profiler or use SET SHOWPLAN_ALL ON or SET SHOWPLAN_XML ON statements as alternative (see Example 2)

--EXAMPLE1:

USE MyDatabase

SET STATISTICS TIME OFF;

set nocount on;

DECLARE @indexRow INT = 0, @cnt int

SET STATISTICS TIME ON

WHILE @indexRow <= 500

BEGIN

       SELECT @cnt=COUNT(*), @indexRow = @indexRow + 1 FROM sys.objects

END          

SET STATISTICS TIME OFF

--EXAMPLE2:

SET SHOWPLAN_ALL ON;

GO

DECLARE @indexRow INT = 0, @cnt int

WHILE @indexRow <= 500

BEGIN

       SELECT @cnt=COUNT(*), @indexRow = @indexRow + 1 FROM sys.objects

END

GO

SET SHOWPLAN_ALL OFF;

GO

Convert full datetime to start of the day in T-SQL

Nice trick to convert full datetime to just year-month-day portion, resetting hours, minutes, seconds. Works in T-SQL, SQL Server

This statement:
SELECT GETDATE() AS FullDate, DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101') AS StartOfTheDay

Produces this result:
FullDate StartOfTheDay

2012-04-11 13:51:15.570 2012-04-11 00:00:00.000


Note1: you can put any datetime instead of GETDATE()

Note2: DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101') is the functional equivalent of DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Dropping all stored procedures, functions, views and also system-generated constraints

The script below will drop all stored procedures, functions and views in the database. Very useful when doing upgrades. It will also drop all system-generated default constraints. Just make sure that from now on, in your DB upgrade script, all the constraints have predefined names.

Works in SQL Server 2008

DECLARE @schemaName varchar(100), @objName varchar(500), @objType varchar(2), @parentObjName varchar(500)
DECLARE cur CURSOR
FOR SELECT sa.name AS SchemaName, s.[name] AS ObjName, s.[type] AS ObjType, s2.[name] AS ParentObjName
    FROM sys.objects s
    INNER JOIN sys.schemas sa ON s.schema_id = sa.schema_id
    LEFT OUTER JOIN sys.objects s2 ON s2.object_id = s.parent_object_id   
    WHERE s.[name] NOT LIKE '%aspnet%' AND s.[type] in (N'P', N'PC', N'V', N'FN', N'TF') OR (s.[name] like '%[_][_]%' AND s.[name] NOT LIKE '%aspnet%')

--ignoring constraints on ASP.Net tables as they are not touched by the upgrade
OPEN cur

FETCH NEXT FROM cur INTO @schemaName, @objName, @objType, @parentObjName
WHILE @@fetch_status = 0
BEGIN
    PRINT 'Dropping ['+@objName+'] ...'
    IF @objType = N'V'
    BEGIN
        EXEC (N'DROP VIEW ['+@schemaName+'].['+@objName+']')
    END
    IF @objType IN (N'P', N'PC')
    BEGIN
        EXEC (N'DROP PROCEDURE ['+@schemaName+'].['+@objName+']')
    END
    IF @objType IN (N'FN', N'TF')
    BEGIN
        EXEC (N'DROP FUNCTION ['+@schemaName+'].['+@objName+']')
    END   
    IF @objType IN (N'D') AND @parentObjName IS NOT NULL
    BEGIN
        PRINT 'Will drop the '+ @objName +' constraint in ' + @parentObjName
        EXEC (N'ALTER TABLE ['+@schemaName+'].['+@parentObjName+'] DROP CONSTRAINT ['+@objName+']');
    END       
    FETCH NEXT FROM cur INTO @schemaName, @objName, @objType, @parentObjName
END
CLOSE cur
DEALLOCATE cur
GO

Alternatives to SQLite or SQL Server Compact

Wanted to find suitable local table storage for some quick data transformation engine. Didn't want to use tempdb or create\drop tables in my SQL Server DB.

SQLite.org:
Pros: just one dll, no install needed
Cons:
No Foreign Keys. Write locks the whole DB

SQL Server Compact Edition (CE) 4.0:
http://weblogs.asp.net/scottgu/archive/2011/01/11/vs-2010-sp1-and-sql-ce.aspx
http://www.microsoft.com/download/en/details.aspx?id=17876
Toolbox: http://sqlcetoolbox.codeplex.com/

works in a multi-threaded environment.  Previous releases of SQL CE only worked in client-apps and could corrupt/crash in server environments.  SQL CE 4 specifically supports server scenarios.
in-process; transactions; no XQuery or XML support; no SPs; DB size - .sdf file up to 4Gb
Management UI: VS2010 IDE, SQL Server Management Studio
Needs Visual C++ 2008 Runtime Libraries - installed with .Net 3.5 SP1
need to install tools for VS2010SP1 : http://go.microsoft.com/fwlink/?LinkId=212219

Other alternatives (to be continued):

http://www.firebirdsql.org/