devmosman Administration

Where is index location on Database?


Where is index location on Database? 

Hi guys today I will show smoothing is easy but more important to know about it {Where is the index live} by the below DMV we can return all the index with table name and with File group hosted on it , another thing you can use this DMV to know the heap tables , Clustered index , non-Clustered index

Index Location:

WITH C AS
(

SELECT ps.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
JOIN sys.destination_data_spaces dds ON dds.data_space_id = f.data_space_id
JOIN sys.partition_schemes ps ON ps.data_space_id = dds.partition_scheme_id

UNION

SELECT f.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
)
–SELECT * FROM c
SELECT [ObjectName] = OBJECT_NAME(i.[object_id])
, [IndexID] = i.[index_id]
, [IndexName] = i.[name]
, [IndexType] = i.[type_desc]
, [Partitioned] = CASE WHEN ps.data_space_id IS NULL THEN ‘No’
ELSE ‘Yes’
END
, [StorageName] = ISNULL(ps.name, f.name)
, [FileGroupPaths] = CAST(( SELECT name AS “FileGroup”
, physical_name AS “DatabaseFile”
FROM C

WHERE i.data_space_id = c.data_space_id
FOR
XML PATH(”)
) AS XML)
FROM [sys].[indexes] i
LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups f ON f.data_space_id = i.data_space_id
WHERE OBJECTPROPERTY(i.[object_id], ‘IsUserTable’) = 1
ORDER BY [ObjectName], [IndexName]

index

Follow me because next post I will explain everything about the index and How you can build you index model How you can Enhance your expensive query by index more Secrets in the index …ETC

 

Mustafa EL-Masry SQL Server 2014

Create Clustered ColumnStore index for All tables


Problem :

we are working in big project SQL Server 2014 upgrade and SQL Server 2014 Performance this project our role to test the new feature in SQL Server 2014 from the performance wise so we decide  to deploy the new Enhancement in SQL Server 2014 (Clustered ColumnStore index ) CCI so i take backup from one database in my work environment and restored it again with prefix _2014 .

this new type of index to create it on the table this table should be no index on it so i drooped all the index from all the tables but the Problem now How can i create the Clustered columnstore index on all tables( tables supported CCI) by one Click.

Solution :

i do one script to create Clustered columnstore index on all supported tables and here the description of the Script

1- to create CCI on any table we have some limitations one of this limitations the data type of the Columns CCI not supported Some data type.

2- I inserted the data type not supported by CCI in temp Tables

CREATE TABLE #CCI_DataType_Limitation ( DataType NVARCHAR(MAX) )
INSERT INTO #CCI_DataType_Limitation
VALUES ( ‘text’ ),
( ‘timestamp’ ),
( ‘hierarchyid’ ),
( ‘Sql_variant’ ),
( ‘xml’ ),
( ‘geography’ ),
( ‘geometry’ )
Select * from #CCI_DataType_Limitation

Continue reading “Create Clustered ColumnStore index for All tables” →

Mustafa EL-Masry Administration, SQl server Administration

How to monitor your SQL Server instances..(2/10)?


in the previous post we Speaking about our idea for this Series of posts and i Started my First post with How to Diagnosis MSSQL Server or MSSQL Database with fast way ..? Today i will complete this part from How to Diagnosis  Your Database,

Database Information : in this Script we can cover this below points

  • How many databases are on the instance?
  • What recovery models are they using?
  • What is the log reuse wait description?
  • How full are the transaction logs ?
  • What compatibility level are they on?
  • What is the Page Verify Option?
  • Make sure auto_shrink and auto_close are not enabled!

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
db.[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N’Log File(s) Used Size (KB)%’
AND ls.counter_name LIKE N’Log File(s) Size (KB)%’
AND ls.cntr_value > 0 OPTION (RECOMPILE);

Check Database File and all user database paths :

SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

How to Check the VLF count for All Databases:

CREATE TABLE #VLFInfo (RecoveryUnitID int, FileID int,
FileSize bigint, StartOffset bigint,
FSeqNo bigint, [Status] bigint,
Parity bigint, CreateLSN numeric(38));

CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);

EXEC sp_MSforeachdb N’Use [?];

INSERT INTO #VLFInfo
EXEC sp_executesql N”DBCC LOGINFO([?])”;

INSERT INTO #VLFCountResults
SELECT DB_NAME(), COUNT(*)
FROM #VLFInfo;

TRUNCATE TABLE #VLFInfo;’

SELECT DatabaseName, VLFCount
FROM #VLFCountResults
ORDER BY VLFCount DESC;

DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;

 

How to get CPU utilization by database :

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N’dbid’) AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 — system databases
AND DatabaseID <> 32767 — ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

Login_Name list with the Session Count:

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

Check Adhoc Query:

SELECT TOP(20) [text] AS [QueryText], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N’Compiled Plan’
AND cp.objtype = N’Adhoc’
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE)

Top Cash quires by Execution count :

SELECT TOP (250) qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,
total_worker_time, total_logical_reads, 
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
			THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
	  ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text 
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

this not the end i have lot of Scripts can help us to figure the most repeated issue in SQL Server so if you need to know the more Follow me in the Next Post