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

http://msdn.microsoft.com/en-us/library/ms174412.aspx

-- tempdb files

SELECT * FROM tempdb.sys.database_files

GO

Troubleshooting tempdb Space Usage:

http://www.madeira.co.il/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.

http://stackoverflow.com/questions/7631546/entity-framework-row-size-greater-than-allowable-maximum-row-size-of-8060

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:

http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

-- 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