The most costy queries by CPU

Rédigé par M Sozezzo - - Aucun commentaire

Before to start to change everything, you must have a baseline that it established in normal load operation for comparisons.

, DB_NAME(CAST(pa.value as INT)) as DatabaseName
, qs.execution_count
, qs.total_worker_time AS Total_CPU
, total_CPU_inSeconds = qs.total_worker_time/1000000
, average_CPU_inSeconds = (qs.total_worker_time/1000000) / qs.execution_count
, qs.total_elapsed_time
, total_elapsed_time_inSeconds = qs.total_elapsed_time/1000000
, st.text , qp.query_plan
FROM        sys.dm_exec_query_stats                 AS qs
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)     AS st
cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where pa.attribute = 'dbid'
ORDER BY qs.total_worker_time DESC

You can use SQL Server Performance Dashboard Reports.

Source :


How to Script User and Role Object Permissions in SQL Server for Database

Rédigé par M Sozezzo - - Aucun commentaire

This article is nice ( we have a little bug ) , and the SQL script is not execute-safe (if you run twice, you have errors ).

It is for a specific database, when you need to save all SQL Server, you must run for each database.

Lire la suite de How to Script User and Role Object Permissions in SQL Server for Database

Search columns in SQL Server 2005 database

Rédigé par M Sozezzo - - Aucun commentaire

-- Since SQL Server 2005 Management Studio lacks the Object Search feature, here is the simple query to find any column in a database

Select objectName, ColumnName
from sys.columns C inner join sys.objects O
ON C.object_id=O.object_id
where like ‘%ColumntoFind%’order by,

-- This query works for SQL Server 20005. Just replace “ColumnToFind” with your required column name.

, AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
, AS TypeName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
where like '%ColumntoFind%'

-- Site d'origine : Pinal Dave ( )
-- Site d'origine :

Fil RSS des articles de ce mot clé