Create Statistics <StatisticsName>
On <TableName>(<columns list>)
Where <condition>
Example:
CREATE STATISTICS ContactPromotion1
ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key
Error message:
There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key
Explanation:
The foreign key must reference:
- Primary key,
or:
- a field with a unique constraint.
Solution:
If you can edit the PK - do it. If not - alter/create a fit unique constraint.
Small and simple points that can make huge improvement of performance
In the most cases, small and simple points that can make huge improvement of performance - more than complicated SQL tricks. A little thinking is usually the best thing.
Here are some examples.
Don't do unnecessary JOINs!
Don't think that "it's better to bring more data - it can't make a damage" - it can! it can make more SQL work, more data, more calculations, etc.
Don't do LEFT JOIN where you can DO JOIN!
The explanation is simple: INNER JOIN looking for the match, and if it don't find it - it goes on, and LEFT JOIN don't filter, so it continue to scan the entire table. This causes that INNER JOIN uses indexes, etc. in order to be efficient and quick, what LEFT JOIN can't do.
As in the previous tip - don't think "it's better to bring more data - it can't make a damage".
Filter!
When it's required and when you can - filter (using Where clauses). Filter also can use indexes, etc. that wouldn't be used without it.
I improved performance of the query from 15 minutes to 1 second simply by add a WHERE clause. In most cases, the addition of this condition is also important both correct and logical - not only for performance.
Locks details
Get current locks details:
SELECT EC2.session_id AS 'Blocker Process Id',
ES2.login_name AS 'Blocker Login Name',
ES2.program_name AS 'Blocker Program',
DB_Name(ER2.database_id) AS 'Blocked DB Name',
ES2.host_name AS 'Blocker Computer',
CA2.[text] AS 'Blocker Command',
EC1.session_id AS 'Blocked Process Id',
ES1.login_name AS 'Blocked Login Name',
ES1.program_name AS 'Blocked Program',
DB_Name(ER1.database_id) AS 'Blocker DB Name',
ES1.host_name AS 'Blocked Computer',
CA1.[text] AS 'Blocked Command',
Cast(Cast((WT1.wait_duration_ms/60000.0) As Decimal(15,2)) As VarChar) + ' min.'
AS 'Blocked Time',
(Case When WT2.session_id Is Null Then 'Top level blocker' Else 'Secondary blocker' End)
AS 'Block Level'
FROM sys.dm_exec_connections EC1
INNER JOIN sys.dm_exec_sessions ES1 ON EC1.session_id=ES1.session_id
INNER JOIN sys.dm_exec_requests ER1 ON EC1.session_id=ER1.session_id
CROSS APPLY sys.dm_exec_sql_text(EC1.most_recent_sql_handle) CA1
INNER JOIN sys.dm_os_waiting_tasks WT1 ON EC1.session_id=WT1.session_id
LEFT JOIN sys.dm_os_waiting_tasks WT2 ON WT1.blocking_session_id=WT2.session_id
INNER JOIN sys.dm_exec_connections EC2 ON WT1.blocking_session_id=EC2.session_id
INNER JOIN sys.dm_exec_sessions ES2 ON EC2.session_id=ES2.session_id
INNER JOIN sys.dm_exec_requests ER2 ON EC2.session_id=ER2.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(EC2.most_recent_sql_handle) CA2
ORDER BY 'Blocked Time' Desc
Move SQL server database files
-- 1. set DB offline:
ALTER DATABASE MyDataBase SET OFFLINE;
-- 2. move the files
ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile', FILENAME = 'E:\SQL\Data\MyBbFile.mdf' );
ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile_log', FILENAME = 'E:\SQL\Log\MyBbFile_log.ldf' );
ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile_BLOBStorage', FILENAME = 'E:\SQL\BLOB\MyBbFile.ndf' );
-- 3. copy-paste the files to the new folder (via Windows)
-- 4. set DB online:
ALTER DATABASE MyDataBase SET ONLINE;
-- get data of the DB files:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyDataBase');
Get tables size
SELECT t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.Name, p.Rows
ORDER BY TotalSpaceKB desc
Get aggregations of few columns for each record - using 'Values' operator
SELECT ValuesTestID, ValuesTestName,
IntCol1, IntCol2, IntCol3, IntCol4, IntCol5, IntCol6,
( Select MAX(ValuesTestName)
From (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6))
UniqueColumn(ValuesTestName)
) MaxIntCol,
( Select SUM(ValuesTestName)
From (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6))
UniqueColumn(ValuesTestName)
) SumIntCol
FROM ValuesTest
Get DBs files locations and drive details
SELECT DISTINCT
dovs.database_id as database_id,
DB_NAME(dovs.database_id) as DBName,
mf.physical_name as PhysicalFileLocation,
dovs.logical_volume_name as LogicalDriveName,
dovs.volume_mount_point as Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY DB_NAME(dovs.database_id)
Extract data from nvarchar column
The case:
We have a log table that include Message column (nvarchar).
We need to extract from the message some data, for example: Entity ID and Entity Type.
SELECT LogId,
(case when CHARINDEX ('EntityID:', Msg) > 0
then substring ( Msg,
CHARINDEX ('EntityID:', Msg) + 9, -- 9=length of 'EntityID:'
( case when CHARINDEX ('EntityID:', Msg) > 0
then ( CHARINDEX (',', Msg, CHARINDEX ('EntityID:', Msg))
- CHARINDEX ('EntityID:', Msg) - 9) -- 9=length of 'EntityID:'
else CHARINDEX ('EntityID:', Msg) + 9 -- 9=length of 'EntityID:'
end
)
)
else NULL
end ) as EntityID,
(case when CHARINDEX ('EntityType:', Msg) > 0
then substring ( Msg,
CHARINDEX ('EntityType:', Msg) + 11, -- 11=length of 'EntityID:'
( case when CHARINDEX ('EntityType:', Msg) > 0
then ( CHARINDEX (',', Msg, CHARINDEX ('EntityType:', Msg))
- CHARINDEX ('EntityType:', Msg) - 11) -- 11=length of 'EntityID:'
else CHARINDEX ('EntityType:', Msg) + 11 -- 11=length of 'EntityID:'
end
)
)
else NULL
end ) as EntityType
FROM OurLogTable
Find out who modified an object in SQL Server
SELECT --T.*, 'I', I.*, 'E', E.*
E.trace_event_id, E.category_id, E.name,
T.[path], T.start_time, T.last_event_time, T.event_count,
I.NTUserName, I.NTDomainName, I.HostName,
I.ClientProcessID, I.ApplicationName,
I.LoginName, I.SPID, I.ObjectID, I.ServerName,
I.ObjectName, I.DatabaseName, I.SessionLoginName,
I.TextData, I.BinaryData
FROM sys.traces T
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I
JOIN sys.trace_events E On I.eventclass = E.trace_event_id
WHERE T.id = 1
AND E.name = 'Object:Altered' -- or 'Object:Deleted' or ...
--AND ObjectName like '%AnObjectName%' -- <- filter for a specific object
ORDER BY T.start_time desc
Get columns list of an index
SELECT i.[object_id] as TableID, object_name(i.[object_id]) as TableName,
i.index_id as IndexID, i.[name] as IndexName,
i.is_primary_key, i.type_desc as IndexTypeDesc,
ic.column_id, c.[name] as column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.index_id = ic.index_id
AND i.[object_id] = ic.[object_id]
JOIN sys.columns c ON ic.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
-- optional filters:
WHERE object_name(i.[object_id]) = 'TableName'
WHERE i.[name] = 'IndexName'
WHERE c.[name] = 'ColumnName'
tempdb files and usage
-- space usage information for each data file in tempdb
SELECT *,
(user_object_reserved_page_count)*8 as usr_obj_kb,
(internal_object_reserved_page_count)*8 as internal_obj_kb,
(version_store_reserved_page_count)*8 as version_store_kb
FROM sys.dm_db_file_space_usage
GO
-- tempdb files
SELECT * FROM tempdb.sys.database_files
GO
Troubleshooting tempdb Space Usage:
Resets the wait statistics for an instance of SQL Server
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
Group by with Rollup
GROUP BY .... WITH ROLLUP
"with Rollup" uses to aggregate sub-groups in a group statement.
An example will be better than any explanation:
Cannot create a row of size which is greater than the allowable maximum row size of
Error message:
Cannot create a row of size 8066 which is greater than the allowable maximum row size of 8060.
The case:
I tried to alter a column from nvarchar(max) to nvarchar(1500).
I run:
UPDATE MyTable set Col1 = Left(Col1, 1500) where len(Col1) > 1500
ALTER TABLE [dbo].[MyTable] ALTER COLUMN [Col1] [nvarchar](1500) NULL
And I get the error message for the ALTER TABLE statement.
Solution:
ALTER TABLE [dbo].[MyTable] REBUILD -- before the ALTER COLUMN statement.
Find column dependency
select OBJECT_NAME(k.parent_object_id) as parentTable,
c1.name as parentColumn,
OBJECT_NAME(k.referenced_object_id) as referencedTable,
c2.name as referencedColumn,
k.name
from sys.foreign_keys k
join sys.foreign_key_columns f on f.parent_object_id = k.parent_object_id
join sys.columns c1 on c1.column_id = f.parent_column_id
and c1.object_id = k.parent_object_id
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'
select top n WITH TIES
"WITH TIES" is a case of TOP:
SELECT TOP N WITH TIES ...
FROM Table Name ORDER BY ColumnA
WITH TIES Specifies that additional rows (more than N) be returned from the base result set with the same value of the last of the TOP N.
Example:
Top 3 - returns exactly 3 records. Which one from the 4 records that can be chosen? who knows...
Top 3 With Ties - returns with the third record all the appropriate records.
Notes:
- WITH TIES can return more than N records. It's important to remember it.
- WITH TIES is used only with ORDER BY clause. If we don't use the ORDER BY clause an error will be generated.
Missing Index Script
The Author is Pinal Dave:
-- Original Author: Pinal Dave (C) 2011
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
Data Type Precedence
When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.
This conversion can have a big influence regarding performance.
For the precedence order for data types see:
Errors while Indexed View creation
Here are fer errors (and solutions...) while Indexed View creation.
Error:
Cannot schema bind view because name is invalid for schema binding. Names must be in two-part format and an object cannot reference itself
Solution:
Add schema to the objects names.
Error:
Cannot schema bind view 'dbo.MyView'. 'dbo.OtherView' is not schema bound.
Solution:
Use tables, not views.
Another solution: Create the other views 'WITH SCHEMABINDING' also.
Error:
Cannot create index on view . The view contains a self join on
Solution:
Sorry, no solution...... :-(
Indexed View creation
To create indexed view it's the simpler than we cam imaging...
Just add an index on the view:
CREATE UNIQUE CLUSTERED INDEX [IX_V_IndexName] ON [dbo].[MyView]
(
Col1 DESC,
Col2 DESC
)
GO
Subscribe to:
Posts (Atom)