REF: Compression and its Effects on Performance

\ by

I want to remember this study by Erin Stellato.

The summary:

If you’re in an OLTP system, you don’t want to return that many rows, so the tests here should give you an idea of how compression may affect performance. If you’re in a data warehouse, then you will probably see higher duration along with the higher CPU when returning large data sets. While the COMPRESS function provides significant space savings compared to page and row compression, the performance hit in terms of CPU, and the inability to index the compressed columns due to their data type, make it viable only for large volumes of data that will not be searched.

Posted in: Performance, SQL

SQL: Fast Table RowCount

\ by

We aren’t looking for accuracy, so this works fine for us. The NOLOCKs helped e.g. when indexes were in the process of creating.

Fast way to count large tables.

SELECT
    SCHEMA_NAME([Tables].[schema_id]) AS [SchemaName],
    [Tables].[name] AS [TableName],
    SUM([Partitions].[rows]) AS [TotalRowCount]
FROM [sys].[tables] AS [Tables] WITH (NOLOCK)
JOIN [sys].[partitions] AS [Partitions] WITH (NOLOCK)
    ON [Tables].[object_id] = [Partitions].[object_id]
       AND [Partitions].[index_id] IN (
               0, 1
           )
WHERE [Tables].name = N'TRANSACTIONS'
--	and [Partitions].[rows] > 0
GROUP BY SCHEMA_NAME([Tables].[schema_id]),
         [Tables].[name];
Posted in: Coding, Performance, SQL

SQL Server – Monitor Blocking

\ by

Here’s how to write blocking messages out to the SQL Errorlog. It won’t look pretty but has everything you should need.


/*
Set up a trace to monitor blocking on a server.

*/

-- Trace locks into the Errolog file
DBCC TRACEON(1222, -1);
-- Check the status
DBCC TRACESTATUS (1222, -1)
-- and turn it off
DBCC TRACEOFF (1222,-1)

Here is a link which tells how to set up the SQL Profiler to monitor deadlocks using a Deadlock Graph. Choose “Save Deadlock XML Separately” in the new tab and “Each deadlock XML graph in a distinct file” as options and give it a path and filename. This way you can capture and read each lock easily, later.

Find any current locking … blocking for a particular database. I haven’t tried this.


/*

http://stackoverflow.com/questions/12422986/sql-query-to-get-the-deadlocks-in-sql-server-2008

*/

SELECT L.request_session_id AS SPID,
 DB_NAME(L.resource_database_id) AS DatabaseName,
 O.Name AS LockedObjectName,
 P.object_id AS LockedObjectId,
 L.resource_type AS LockedResource,
 L.request_mode AS LockType,
 ST.text AS SqlStatementText,
 ES.login_name AS LoginName,
 ES.host_name AS HostName,
 TST.is_user_transaction AS IsUserTransaction,
 AT.name AS TransactionName,
 CN.auth_scheme AS AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P
ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O
ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES
ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST
ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT
ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN
ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = DB_ID()
ORDER BY L.request_session_id
Posted in: Performance, SQL, SQL Server