RSS

Monthly Archives: March 2014

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 


 



		
 

Tags: , , ,

SQL SERVER – Find Primary Key Using SQL Server Management Studio

 
Leave a comment

Posted by on March 25, 2014 in General topics

 

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

Any DBA or DB Analyst need to know two important point

How to administrate DBs in a single click..?

How to monitor your SQL Server instances..?

this from my opinion first point  How to administrate DBs in a single click..? we covered the most important subject in this point in this Series posted by SHEHAP EL-NAGAR :

How to administrate your databases in a single click..?! (1/8)

How to administrate your databases in a single click..?! (2/8)

How to administrate your databases in a single click..?! (3/8)

How to administrate your databases in a single click..?! (4/8)

How to administrate your databases in a single click..?! (5/8)

How to administrate your databases in a single click ..?! (6/8)

Follow up  SHEHAP EL-NAGAR to complete this wonderful and amazing series thanks for AMRO SELIM for his Constructive and fruitful collaboration in this Series .

but this not the end because we will start very Wonderful and Impressive Series to complete our plan to help any DBA or DB Analyst in his/here job now you can mange your database  and administrate it but How to monitor your SQL Server instances..?

1- How to Diagnosis MSSQL Server or MSSQL Database with fast way ..?

2- How to Administrate and Monitor SQL Server Agent by T-SQL..?

3- How to Administrate and Monitor SQL Server Backup and Restore by T-SQL..?

4- How to Audit your SQL Server instance and database ..?

5-How To Monitor your instance and Database by Using ( DMV , Alerts , UCP Moitor , Third party tools..Etc)..?

and most of Question and topic i will cover it in this series to be repository for any DBA to Administrate and monitor his/here SQL Sever instance and Databases it will be DBA portfolio. Let’s Start

How to Diagnosis MSSQL Server or MSSQL Database with fast way ..?

At the first the goal from this post is making repository for scripts can help any DBA or DB Analyst to do Diagnoses on any MSSQL instance or Database by fast and easy way I Collected it after more Search and from the past Experience so if anyone has good script or idea can share it with us and we will share it by his Name

First thing instance level:

SQL and OS Version information :

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [MSSQL OS info];

Installation Data for MSSQL:

SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date]
FROM sys.syslogins
WHERE [sid] = 0x010100000000000512000000;

Server properties:

SELECT SERVERPROPERTY('MachineName'AS [MachineName],
SERVERPROPERTY('ServerName'AS [ServerName],
SERVERPROPERTY('InstanceName'AS [Instance],
SERVERPROPERTY('IsClustered'AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS'AS[ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition'AS [Edition],
SERVERPROPERTY('ProductLevel'AS [ProductLevel],
SERVERPROPERTY('ProductVersion'AS [ProductVersion],
SERVERPROPERTY('ProcessID'AS [ProcessID],
SERVERPROPERTY('Collation'AS [Collation],
SERVERPROPERTY('IsFullTextInstalled'AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly'AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('IsHadrEnabled'AS [IsHadrEnabled],
SERVERPROPERTY('HadrManagerStatus'AS [HadrManagerStatus];

SQL Server Services information :

SELECT servicename, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename
FROM sys.dm_server_services OPTION (RECOMPILE);

Hardware information for SQL Server Instance:

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)],
 committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count],
affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time],
virtual_machine_type_desc AS [Virtual Machine Type]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

Where the SQL Server Error log is located

SELECT is_enabled, [path], max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION(RECOMPILE);

Information about OS Cluster :

</pre>
SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,
 SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout
FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);

Instance Configuration Value:

SELECT name, value, value_in_use, [description]
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);

I am not the owner for this Scripts only i do wide Search on the internet to Collect it Follow me in the Next post to know more Scripts About how we can Diagnose MSSQL Instance|Database.

 
4 Comments

Posted by on March 17, 2014 in SQl server Administration