SELECT TOP 50
QueryState.execution_count
,OBJECT_NAME(objectid)
,query_text = SUBSTRING(
qt.text,
QueryState.statement_start_offset/2,
(CASE WHEN QueryState.statement_end_offset = -1
THEN len(convert(nvarchar(max), qt.text)) * 2
ELSE QueryState.statement_end_offset
END - QueryState.statement_start_offset)/2)
,qt.dbid
,dbname = db_name(qt.dbid)
,qt.objectid
FROM sys.dm_exec_query_stats QueryState
CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) as qt
ORDER BY QueryState.execution_count DESC
Tuesday, September 18, 2018
Get all queries running against any specific table
/*
Get list of all queries hitting any specific tables
*/
SELECT DISTINCT TOP 100
ProcedureName = OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)
,SQLStatement = SUBSTRING(
sqlTxt.Text
,(QueryState.statement_start_offset/2)+1
,CASE QueryState.statement_end_offset
WHEN -1 THEN DATALENGTH(sqlTxt.text)
ELSE QueryState.statement_end_offset
END - (QueryState.statement_start_offset/2) + 1
)
,DiskReads = QueryState.total_physical_reads --- Disk reads
,MemoryReads = QueryState.total_logical_reads --–Logical Reads are memory reads
,ExecutionCount = QueryState.execution_count --Execution Count
,CPUTime = QueryState.total_worker_time --CPU Time
,DiskWaitAndCPUTime = QueryState.total_elapsed_time
,MemoryWrites = QueryState.max_logical_writes
,DateCached = QueryState.creation_time
,DatabaseName = DB_Name(sqlTxt.dbid) --Database name
,LastExecutionTime = QueryState.last_execution_time
,cte.*
FROM sys.dm_exec_query_stats AS QueryState
CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) AS sqlTxt
CROSS APPLY sys.dm_sql_referenced_entities(
OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)
, 'OBJECT'
) cte
WHERE sqlTxt.dbid = db_id() --Get detail for current database
AND cte.referenced_schema_name + '.' + cte.referenced_entity_name = 'dbo.tblEmployee'
Monday, April 10, 2017
Missing index with create statements
Missing index :- Scripts will help you to get all the missing index and prepare script for new index.
/*
Description:- This Query will provide you detail of missing indexes on a table and also
prepare sql script for new index
*/
SELECT
DISTINCT
@@SERVERNAME AS [ServerName]
, DB_NAME() AS [DatabaseName]
, SCHEMA_NAME([systemObject].[schema_id]) AS [SchemaName]
,[systemObject].Object_ID AS ObjectID
, [systemObject].[name] AS [ObjectName]
, CASE [systemObject].[type]
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
ELSE 'Unknown'
END AS [ObjectType]
, [MissingIndexDetail].[equality_columns] AS [EqualityColumns]
, [MissingIndexDetail].[inequality_columns] AS [InequalityColumns]
, [MissingIndexDetail].[included_columns] AS [IncludedColumns]
, [MissingIndexGroupState].[user_seeks] AS [ExpectedIndexSeeksByUserQueries]
, [MissingIndexGroupState].[user_scans] AS [ExpectedIndexScansByUserQueries]
, [MissingIndexGroupState].[last_user_seek] AS [ExpectedLastIndexSeekByUserQueries]
, [MissingIndexGroupState].[last_user_scan] AS [ExpectedLastIndexScanByUserQueries]
, [MissingIndexGroupState].[avg_total_user_cost] AS [ExpectedAvgUserQueriesCostReduction]
, [MissingIndexGroupState].[avg_user_impact] AS [ExpectedAvgUserQueriesBenefitPct],
IndexName='[ix_' + tbl.name + N'_'
+ REPLACE(REPLACE(REPLACE(REPLACE(
ISNULL(equality_columns,N'')+
CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
+ ISNULL(inequality_columns,''),',','')
,'[',''),']',''),' ','_')
+ CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N']' ,
IndexCreateScripts=N'CREATE INDEX [ix_' + tbl.name + N'_'
+ REPLACE(REPLACE(REPLACE(REPLACE(
ISNULL(equality_columns,N'')+
CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
+ ISNULL(inequality_columns,''),',','')
,'[',''),']',''),' ','_')
+ CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N'] ON '
+ [statement] + N' (' + ISNULL(equality_columns,N'')
+ CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N', ' ELSE N'' END
+ CASE WHEN inequality_columns IS NOT NULL THEN inequality_columns ELSE N'' END +
') ' + CASE WHEN included_columns IS NOT NULL THEN N' INCLUDE (' + included_columns + N')' ELSE N'' END
+ N' WITH ('
+ N'FILLFACTOR=80, ONLINE=ON'
+ N')'
+ N';'
FROM
[sys].[dm_db_missing_index_details] AS [MissingIndexDetail]
CROSS APPLY sys.dm_db_missing_index_columns ([MissingIndexDetail].index_handle)
LEFT JOIN [sys].[dm_db_missing_index_groups] AS [MissingIndexGroup]
ON [MissingIndexDetail].[index_handle] = [MissingIndexGroup].[index_handle]
LEFT JOIN [sys].[dm_db_missing_index_group_stats] AS [MissingIndexGroupState]
ON [MissingIndexGroup].[index_group_handle] = [MissingIndexGroupState].[group_handle]
INNER JOIN [sys].[objects] AS [systemObject]
ON [MissingIndexDetail].[object_id] = [systemObject].[object_id]
inner join sys.databases db on [MissingIndexDetail].database_id=db.database_id
inner join sys.tables tbl on [systemObject].object_id=tbl.object_id
WHERE
[MissingIndexDetail].[database_id] = DB_ID() -- Look in the Current Database
AND [systemObject].[type] IN ('U','V') -- Look in Tables & Views
AND [systemObject].[is_ms_shipped] = 0x0 -- Exclude System Generated Objects
--and( tbl.name like '%Accounting%' or tbl.name like '%treasury%') --Add table name for filter
--and [MissingIndexGroupState].[avg_user_impact]>=50; -- filter impact percentage
go
Thursday, December 29, 2016
How we can determine table column dependencies within a SQL database?
I came a situation during some analysis where i had to figure
out all dependency of a column across database. This also required when we do
some changes on table columns and wanted to know the impact of such changes.
One way is that we can use SYSCOMMENTS table. It is a table which contains entries for each views, rules,
default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure.
The column TEXT in the syscomments table contains the actual code for all these
objects, and knowing it you can write a code to check the dependencies. I am explaining different approach.
/* Table Name for
which we need to find the dependency of columns.*/
Declare @TableName
varchar(max)='tblSupplierRequestOrder'
/* Column name for
which we need to find the dependnecy in a database*/
Declare @ColumnName
varchar(max)='OrderDate'
select
OBJECT_NAME(k.parent_object_id) as parentTableName
, c1.name as parentColumn
, OBJECT_NAME(k.referenced_object_id)
as referencedTableName
, c2.name as referencedColumn
from
sys.foreign_keys k
inner
join sys.foreign_key_columns
f
on f.parent_object_id = k.parent_object_id
inner
join sys.columns c1
on c1.column_id = f.parent_column_id
and
c1.object_id = k.parent_object_id
inner
join sys.columns c2
on c2.column_id = f.referenced_column_id
and
c2.object_id = k.referenced_object_id
where
c2.name = @ColumnName
and
OBJECT_NAME(k.referenced_object_id)
=@TableName
Friday, December 23, 2016
Column and table constraints
Column and Table Constraints
A column constraint is
specified as part of a column definition and applies only to that column.
A table constraint is
declared independently from a column definition and can apply to more than one
column in a table. Table constraints must be used when more than one column
must be included in a constraint.
For example, if a
table has two or more columns in the primary key, you must use a table
constraint to include both columns in the primary key. Consider a table that
records events occurring in a system.
A number of different event
can occur in different time or same time but assuming no same event occur in
same time and same type.
This can be enforced
in the table by including both the event_type and event_time columns
in a two-column primary key, as shown in the following example.
CREATE TABLE System_process
(event_typeID int,
event_time datetime,
event_site char(50),
event_desc char(1024),
CONSTRAINT event_key PRIMARY KEY (event_typeID , event_time) )
What is Constraints in TSQL?
Constraints let you define the way the Database
Engine automatically enforces the integrity of a database. Constraints define
rules regarding the values allowed in columns and are the standard mechanism
for enforcing integrity.Constraints is preferred to using DML
Triggers, rules, and defaults. The query optimizer also uses constraint definitions
to build high-performance query execution plans.
Types of Constraints.
·
NOT NULL, This specify that column does not access NULL values.
·
CHECK constraints enforce domain integrity by limiting the
values that can be put in a column. A check constraint can be Boolean values
(True, false or unknown). We can add search condition which will be applied
while we do insert or update operation on a column. This also enforce that
inserted or updated values must be in range which is specified within check.
CREATE TABLE Employee
(
Employee_id bigint
PRIMARY KEY,
Employee_name varchar(max),
Emp_address varchar(max),
Emp_basic_pay money,
CONSTRAINT chk_id CHECK (Employee_id BETWEEN
100 and 1000000 )
)
·
UNIQUE constraints enforce the uniqueness of the values in a set
of columns. This enforce no two rows in a table can have same value for the
columns. Primary keys also enforce uniqueness, but primary keys do not allow
NULL as one of the unique values.
· PRIMARY KEY constraints identify the column or set of columns
that have values that uniquely identify a row in a table. No two
rows in a table can have the same primary key value. You cannot enter NULL for
any column in a primary key.
CREATE TABLE Employee
(
Employee_id bigint
PRIMARY KEY,
Employee_name varchar(max),
Emp_address varchar(max),
‘ Emp_basic_pay money,
CONSTRAINT chk_id CHECK (Employee_id BETWEEN
100 and 1000000 )
)
- FOREIGN KEY constraints identify and enforce the relationships between tables.
A foreign key in one table points to a candidate key in another
table. (A column or combination of columns that
qualify as a primary key value is referred to as a candidate key.)
CREATE TABLE order_part
(
order_nmbr int,
part_nmbr int
FOREIGN
KEY REFERENCES part_sample(part_nmbr)
ON
DELETE NO ACTION,
qty_ordered int
);
GO
You cannot insert a row with a foreign key value, except NULL,
if there is no candidate key with that value. The ON DELETE clause controls
what actions are taken when you try to delete a row to which existing foreign
keys point.
The ON DELETE clause has
the following options:
a. NO ACTION specifies that the deletion fails with
an error.
b. CASCADE specifies that all the rows with foreign
keys pointing to the deleted row are also deleted.
c. SET NULL specifies that all rows with foreign keys
pointing to the deleted row are set to NULL.
d. SET DEFAULT specifies that all rows with foreign
keys pointing to the deleted row are set to their default value
The ON UPDATE clause defines the actions that are taken if you
try to update a candidate key value to which existing foreign keys point. This
clause also supports the NO ACTION, CASCADE, SET NULL and SET DEFAULT options.
Subscribe to:
Posts (Atom)