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.


SELECT TOP 20  
 qs.sql_handle
, 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
go

You can use SQL Server Performance Dashboard Reports.
https://sqldashboards.codeplex.com/

Source :
https://blogs.msdn.microsoft.com/sqltips/2005/10/05/find-top-n-costly-query-plans-in-adhoc-batches-or-modules/

https://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql

 

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 O.name objectName, C.name ColumnName
from sys.columns C inner join sys.objects O
ON C.object_id=O.object_id
where C.name like ‘%ColumntoFind%’order by O.name,C.name

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

SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
where
C.name like '%ColumntoFind%'
ORDER BY c.OBJECT_ID;


-- Site d'origine : Pinal Dave ( http://blog.SQLAuthority.com/ )
-- Site d'origine : http://blog.namwarrizvi.com/

Fil RSS des articles de ce mot clé