Counter of Festivals

Ashok Blog for SQL Lovers

Thursday, 21 May 2015

Dynamic SQL in SQL Server

Dynamic SQL in SQL Server
Using Exec:

declare @sqlcmd varchar(2000)
declare @collist varchar(1000)
declare @pname varchar(1000)

set @pname='''Chai'''

set @collist='Productid,Productname,categoryid'

set @sqlcmd='SELECT '+@collist+' FROM northwind.dbo.Products WHERE productname='+@pname

PRINT(@sqlcmd)

EXEC(@sqlcmd)
When you convert into SP you have to give value as dynamic so you have to

put @pname parameter inside Quote like ’’’+Parameter+ ’’’


ALTER proc dynamicspex(@pname varchar(1000)=NULL)
as

begin

declare @sqlcmd varchar(2000)
declare @collist varchar(1000)

set @collist='Productid,Productname,categoryid'

set @sqlcmd='SELECT '+@collist+' FROM northwind.dbo.Products WHERE productname='''+@pname+''''

PRINT(@sqlcmd)

EXEC(@sqlcmd)

End
EXEC dynamicspex 'Chai'

Using sp_executesql:
declare @sqlcmd nvarchar(2000)
declare @collist varchar(1000)
declare @pname varchar(1000)

set @pname='chai'

set @collist='Productid,Productname,categoryid'

set @sqlcmd='SELECT '+@collist+' FROM northwind.dbo.Products WHERE productname=@pname'

Execute SP_EXECUTESQL @sqlcmd,N'@pname varchar(1000)',@pname=@pname

Dynamic SQL in Stored Procedures

Dynamic SQL allows stored procedures to “write” or dynamically generate their SQL statements. The most common use case for dynamic SQL is stored procedures with optional parameters in the WHERE clause. These are typically called from reports or screens that have multiple, optional search criteria. This article describes how to write these types of stored procedures so they execute well and resist SQL injection attacks.
A simple example of a stored procedure with dynamic SQL is:

use AdventureWorks
GO
IF  EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]') 
   AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO

CREATE PROCEDURE [Sales].[GetSalesOrders] (
 @CustomerID INT = NULL,
 @ContactID INT = NULL,
 @debug bit = 0 )
AS
SET NOCOUNT ON; 

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT @ParameterDefinition = '
 @CustomerParameter INT,
 @ContactParameter INT
';

SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status], 
 [CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';

IF @CustomerID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND CustomerID = @CustomerParameter ';
 
IF @ContactID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND ContactID = @ContactParameter ';
 
IF @debug = 1
 PRINT @SQL
 
EXEC sp_executeSQL 
 @SQL,
 @ParameterDefinition,
 @CustomerParameter = @CustomerID,
 @ContactParameter = @ContactID; 
GO

EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724



Wednesday, 20 May 2015

Dynamic Management Views(DMV's) in SQL Server

Dynamic Management Views(DMV's)

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

1)To See Wait type using DMVs:

1)SELECT DiSTINCT wait_type
FROM sys.dm_os_wait_stats;

2)To check Currently Running Query/Statements in SQL Server:

1)sys.dm_exec_sessions

2)sys.dm_exec_requests


3)To check index details using DMV’s:

sys.dm_db_missing_index_details ----to check missing indexes.

sys.dm_db_missing_index_columns ----to check missing index columns

sys.dm_db_index_usage_stats ----to check index usage

4)To check I/O related info using DMV’s

sys.dm_io_pending_io_requests

5) To check Object Related info using DMV’s

sys.dm_sql_referenced_entities

sys.dm_sql_referencing_entities

6)To see Replication related info using DMV’s:

sys.dm_repl_traninfo

sys.dm_repl_schemas

sys.dm_repl_articles

7) To check audit related info using DMV’s:

sys.dm_audit_actions (Transact-SQL)

8)To check Transaction related info using DMV’s:

sys.dm_tran_locks

sys.dm_tran_database_transactions

sys.dm_tran_active_transactions

9)To check Operationg Systems info using DMV’s:

sys.dm_os_performance_counters

sys.dm_os_memory_objects

sys.dm_os_process_memory 

sys.dm_os_waiting_tasks




Currently Running Queries to track what happening/currently running  in SQL Server using DMV's?

1)sys.dm_exec_sessions

2)sys.dm_exec_requests

The Above Two DMV's mainly used to see currently running in SQL Server.

T-SQL Script:


DECLARE @OpenQueries TABLE (cpu_time INT, logical_reads INT, session_id INT)
INSERT INTO @OpenQueries(cpu_time, logical_reads, session_id)
select r.cpu_time ,r.logical_reads, r.session_id
from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r 
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
where is_user_process = 1
and s.session_id <> @@SPID
waitfor delay '00:00:01'
select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)  else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text
, r.cpu_time-t.cpu_time as CPUDiff 
, r.logical_reads-t.logical_reads as ReadDiff
, r.wait_type
, r.wait_time
, r.last_wait_type
, r.wait_resource
, r.command
, r.database_id
, r.blocking_session_id
, r.granted_query_memory
, r.session_id
, r.reads
, r.writes, r.row_count, s.[host_name]
, s.program_name, s.login_name
from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r 
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
left join @OpenQueries as t on t.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
where is_user_process = 1
and s.session_id <> @@SPID
order by 3 desc

Check Failed Emails and ReSend Emails Script using DataBaseMail in SQL Server

Check Failed Emails and ReSend Emails  Script using DataBaseMail in SQL Server:

Failed Email Details are saved in below bolded Table in SQL Server

SELECT  mailitem_id,recipients,subject,body,send_request_date FROM msdb.dbo.sysmail_faileditems
where convert(varchar(10),send_request_date,121) Between convert(varchar(10),getdate()-2,121) and convert(varchar(10),getdate(),121)

order by send_request_date desc

So we can see below Script to find and resend Failed Emails using dbmail.

Script:

SET NOCOUNT ON;
GO

DECLARE @EmailFailedTable TABLE(id INT IDENTITY,mailitem_id int,recipients varchar(max),subject varchar(max),body varchar(max))

INSERT INTO @EmailFailedTable (mailitem_id,recipients,subject,body)

SELECT  mailitem_id,recipients,subject,body FROM msdb.dbo.sysmail_faileditems
where convert(varchar,send_request_date,121) between convert(varchar,getdate()-7,121) and convert(varchar,getdate(),121)


DECLARE @Rec VARCHAR(MAX),@Sub varchar(MAX),@body varchar(max)

DECLARE curFailedEmail CURSOR DYNAMIC FOR

SELECT recipients,subject,body  FROM @EmailFailedTable

OPEN curFailedEmail

BEGIN

FETCH NEXT FROM curFailedEmail INTO @Rec,@Sub,@body

WHILE @@FETCH_STATUS = 0
   
BEGIN

PRINT 'Rec : '+ convert(varchar(100),@Rec)+', Sub : '+ convert(varchar(max),@Sub)+ ', body : '+convert(varchar(max),@body)

 EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'Intimate',
@recipients = @Rec,
@body = @body,
@subject = @Sub

FETCH NEXT FROM curFailedEmail INTO @Sub,@body,@Rec
    END
END
CLOSE curFailedEmail

DEALLOCATE curFailedEmail




Tuesday, 12 May 2015

Perfmon Tool in SQL Server to monitor performance of Server

Database Performance Counters

Most DBAs and developers probably use Profiler, trace, review query plans, run sp_who2, run DBCCs, etc... to capture data to figure out what is currently running on the database server.  These tools are great, but don't give you the entire picture in an easy to use way.
Another tool that all DBAs and developers should use is Performance Monitor.  This OS level tool provides great insight into Windows counters, but also into specific SQL Server counters.  There are hundreds of counters that are exposed within this tool and there are several that are specific to SQL Server. 
To launch Performance Monitor, click Start, Run... and type in "perfmon" and the following should open.  This application can also be found under the Administrative Tools.
To add a counter, click on the + icon or use Ctrl + I and the following will open.
Most of the counters are server specific and do not give you insight into each individual database, but the following list of counters are at the database level, so this means you can collect this data for each database that is on the server as well as an overall count for all databases.
If you navigate to the "Databases" performance objects you can see the counters that are specific to SQL Server databases which are also listed below.
CounterDescription
Active TransactionsNumber of active update transactions for the database.
Backup/Restore Throughput per/secRead/write throughput for backup/restore of a database.
Bulk Copy Rows/secNumber of rows bulk copied.
Bulk Copy Throughput/secKiloBytes bulk copied.
Data File(s) Size (KB)The cumulative size of all the data files in the database.
DBCC Logical Scan Bytes/secLogical read scan rate for DBCC commands
Log Bytes Flushed/secTotal number of log bytes flushed.
Log Cache Hit RatioPercentage of log cache reads that were satisfied from the log cache.
Log Cache Reads/secReads performed through the log manager cache.
Log File(s) Size (KB)The cumulative size of all the log files in the database.
Log File(s) Used Size (KB)The cumulative used size of all the log files in the database.
Log Flush Wait TimeTotal wait time (milliseconds).
Log Flush Waits/secNumber of commits waiting on log flush.
Log Flushes/secNumber of log flushes.
Log GrowthsTotal number of log growths for this database.
Log ShrinksTotal number of log shrinks for this database.
Log TruncationsTotal number of log truncations for this database.
Percent Log UsedThe percent of space in the log that is in use.
Repl Pending XactsNumber of pending replication transactions in the database.
Repl Trans RateReplication transaction rate (replicated transactions/sec.).
Shrink Data Movement Bytes/secThe rate data is being moved by Autoshrink, DBCC SHRINKDATABASE or SHRINKFILE.
Transactions/secNumber of transactions started for the database.
Why is this helpfulDepending on your database server, most SQL Servers host multiple databases and applications.  Some of these databases are quite busy and others are not used at all.  When deciding to consolidate servers, move databases to another server or moving databases to different physical disks these counters can show you where the bottlenecks may be and also which databases are more busy than others.
Some of the useful database counters to collect are:
  • Transactions/sec
  • Log Cache Hit Ratio
  • Log Cache Reads/sec
  • Log Bytes Flushed/sec
  • Log Flush Wait Time
  • Log Flush Waits/sec
  • Log Flushes/sec
  • Percent Log Used
By collecting this data for all of your databases you can see where the most activity is occurring.  Also, by using Performance Monitor you can easily graph out and trend these various counters for each of your databases.  Whether you get this information from running DBCC commands, using the DMVs in SQL 2005 or by using Performance Monitor it doesn't really matter, but these are things that should be reviewed and trended to ensure your databases are running as optimally as possible.

How to check Last updated Statistics in Table for SQL Server?

How to check Last updated Statistics in Table for SQL Server:

First - find the statistics you want to check:


Second - see its properties, and there you will see the last updated timestamp:




(Or )
you may want to execute the following query:
SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated 
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'

(Or)

Display the status of all statistics on a table

The following displays the status of all statistics on the Product table.
USE DatabaseName;
GO
EXEC sp_autostats 'TableName';
GO
(Or)
The best way to get information about statistics is through the command
DBCC SHOW_STATISTICS (TableName,indexname)
That will return information not just about when the stats where updated, but their size, density, how selective they are, and the histogram that shows the distribution of data. With all that, you can determine if those stats are up to date and effective.


What Are SQL Server Waits and Wait Types?

what SQL Server has been waiting on when executing queries? 

three categories of waits that could affect any given request:
  • Resource waits are caused by a particular resource, perhaps a specific lock that is unavailable when the requested is submitted. Resource waits are the ones you should focus on for troubleshooting the large majority of performance issues.
  • External waits occur when SQL Server worker thread is waiting on an external process, such as extended stored procedure to be completed. External wait does not necessarily mean that the connection is idle; rather it might mean that SQL Server is executing an external code which it cannot control. Finally the queue waits occur if a worker thread is idle and is waiting for work to be assigned to it.
  • Queue waits normally apply to internal background tasks, such as ghost cleanup, which physically removes records that have been previously deleted. Normally you don't have to worry about any performance degradation due to queue waits.
Some of Wait types:

1)ASYNC_NETWORK_IO - Network
2)BACKUPIO - Backup
3)CXPACKET - Query Used to synchronize threads involved in a parallel query. This wait type only means a parallel query is executing.
4)WAITFOR - Background
5)LCK_M_IXLock
  • ASYNC_NETWORK_IO: the classic cause of this wait type is RBAR (Row-By-Agonizing-Row) processing of results in a client, instead of caching the results client-side and telling SQL Server to send more. A common misconception is that this wait type is usually caused by network problems – that’s rarely the case in my experience.
  • CXPACKET: this wait type always accrues when parallelism happens, as the control thread in a parallel operation waits until all threads have completed. However, when parallel threads are given unbalanced amounts of work to do, the threads that finish early also accrue this wait type, leading to it maybe becoming the most prevalent. So this one could be benign, as the workload has lots of good parallelism, but could be malignant if there’s unwanted parallelism or problems causing skewed distribution of work among parallel threads.
  • LCK_M_IX: this wait type occurs when a thread is waiting for a table or page IX lock so that a row insert or update can occur. It could be from lock escalation to a table X or S lock causing all other threads to wait to be able to insert/update.
  • LCK_M_X: this wait type commonly occurs when lock escalation is happening. It could also be caused by using a restrictive isolation level like REPEATABLE_READ or SERIALIZABLE that requires S and IS locks to be held until the end of a transaction. Note that distributed transactions change the isolation level to SERIALIZABLE under the covers – something that’s bitten several of our clients before we helped them. Someone could also have inhibited row locks on a clustered index causing all inserts to acquire page X locks – this is very uncommon though.
Currently Running Queries to track what happening/currently running  in SQL Server?

T-SQL Script:


DECLARE @OpenQueries TABLE (cpu_time INT, logical_reads INT, session_id INT)
INSERT INTO @OpenQueries(cpu_time, logical_reads, session_id)
select r.cpu_time ,r.logical_reads, r.session_id
from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r 
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
where is_user_process = 1
and s.session_id <> @@SPID
waitfor delay '00:00:01'
select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)  else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text
, r.cpu_time-t.cpu_time as CPUDiff 
, r.logical_reads-t.logical_reads as ReadDiff
, r.wait_type
, r.wait_time
, r.last_wait_type
, r.wait_resource
, r.command
, r.database_id
, r.blocking_session_id
, r.granted_query_memory
, r.session_id
, r.reads
, r.writes, r.row_count, s.[host_name]
, s.program_name, s.login_name
from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r 
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
left join @OpenQueries as t on t.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
where is_user_process = 1
and s.session_id <> @@SPID
order by 3 desc