Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

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




Monday 11 May 2015

Merge Statement in SQL 2008

Merge Command:- 

Merge command is a new feature introduced in the SQL Server 2008


  • It can perform Update, Insert and delete operations at a single statement which means all the data is processed and read only once instead of three times(In case of Insertion, deletion and update statements.)

  •  It has a target table and Source table. These two tables are join based on a conditions and depending upon whether the Condition is matched or not, Update, Insertion and Deletion Operations are performed.



The Syntax of the Merge command is given below:-


MERGE  [AS TARGET]
USING  [AS SOURCE]
ON 
[WHEN MATCHED 
THEN  ] (UPDATE Target table from Source)
[WHEN NOT MATCHED [BY TARGET]
THEN  ](INSERT Target table from Source)
[WHEN NOT MATCHED BY SOURCE
THEN  ];(DELETE Target table)

; semi colon is important in end of merget statement.


select 's' source,* from Import_Employee


select 't' target,* from employee



MERGE employee AS t
USING Import_Employee as s
on t.EmployeeNumber=s.empno
When matched then 
update set T.Firstname=s.Firstname,T.Lastname=s.Lastname
When not matched by target then
insert(EmployeeNumber,firstname,lastname)
values(s.empno,s.firstname,s.lastname)

when not matched by source then
delete;

Writing Merge Command in SQL Server 2008


After Executed Merge command See the results



Thanks for reading!!!!!!!



Wednesday 6 May 2015

Columnstore indexes in SQL 2012 and Sparse Columns in SQL 2008

Columnstore indexes in SQL 2012:

Columnstore indexes in the SQL Server Database Engine can be used to significantly speed-up the processing time of common data warehousing queries.

Ref:

https://msdn.microsoft.com/en-us/library/gg492153(v=sql.110).aspx

It is available From SQL 2012 only.

Sample:

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey);

GO
The following data types can be included in a columnstore index.
  • char and varchar
  • nchar and nvarchar (except varchar(max) and nvarchar(max))
  • decimal (and numeric) (Except with precision greater than 18 digits.)
  • int , bigintsmallint, and tinyint
  • float (and real)
  • bit
  • money and smallmoney
  • All date and time data types (except datetimeoffset with scale greater than 2)
The following data types cannot be included in a columnstore index.
  • binary and varbinary
  • ntext , text, and image
  • varchar(max) and nvarchar(max)
  • uniqueidentifier
  • rowversion (and timestamp)
  • sql_variant
  • decimal (and numeric) with precision greater than 18 digits
  • datetimeoffset with scale greater than 2
  • CLR types (hierarchyid and spatial types)
  • xml
Basic Restrictions
A columnstore index:
  • Cannot have more than 1024 columns.
  • Cannot be clustered. Only nonclustered columnstore indexes are available.
  • Cannot be a unique index.
  • Cannot be created on a view or indexed view.
  • Cannot include a sparse column.
Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent

for more details about sparse column see here


  • Cannot act as a primary key or a foreign key.
  • Cannot be changed using the ALTER INDEX statement. Drop and re-create the columnstore index instead. (You can use ALTER INDEX to disable and rebuild a columnstore index.)
  • Cannot be created by with the INCLUDE keyword.
  • Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms. Sorting would eliminate many of the performance benefits.
Columnstore indexes cannot be combined with the following features:
  • Page and row compression, and vardecimal storage format (A columnstore index is already compressed in a different format.)
  • Replication
  • Change tracking
  • Change data capture
  • Filestream
Table cannot be updated – For SQL Server 2012, a table with a columnstore index cannot be updated. For workarounds, see Best Practices: Updating Data in a Columnstore Index

Tables that have a columnstore index cannot be updated. 
To update a table with a columnstore index, drop the columnstore index, perform any required INSERTDELETEUPDATE, or MERGE operations, and then rebuild the columnstore index.

Choosing Columns for a Column Store Index

Some of the performance benefit of a columnstore index is derived from the compression techniques that reduce the number of data pages that must be read and manipulated to process the query. Compression works best on character or numeric columns that have large amounts of duplicated values. For example, dimension tables might have columns for postal codes, cities, and sales regions. If many postal codes are located in each city, and if many cities are located in each sales region, then the sales region column would be the most compressed, the city column would have somewhat less compression, and the postal code would have the least compression. Although all columns are good candidates for a columnstore index, adding the sales region code column to the columnstore index will achieve the greatest benefit from columnstore compression, and the postal code will achieve the least.
Dimension Table
Postalcodes cities salesregions
City Table
Cityname postalcodes
Salesregions Table
Salesregions Cityname

Sparse Columns in SQL 2008:


Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent

Sparse Column is one more new feature introduced in SQL SERVER 2008. 
Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).
In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. 
In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.

GO
CREATE TABLE SPARSECOLUMNTABLE
(
 col1 int identity(1,1),
 col2 datetime sparse,
 col3 int sparse
)
CREATE TABLE NONSPARSECOLUMNTABLE
(
 col1 int identity(1,1),
 col2 datetime,
 col3 int
)
GO
INSERT INTO SPARSECOLUMNTABLE VALUES(NULL,NULL)
INSERT INTO NONSPARSECOLUMNTABLE VALUES(NULL,NULL)
GO 25000

 Now check the space used by these tables by executing the below statements:
EXEC SP_Spaceused SPARSECOLUMNTABLE 
EXEC SP_Spaceused NONSPARSECOLUMNTABLE
Result:
name              rows        reserved data   index_size unused
SPARSECOLUMNTABLE 25000       392 KB  344 KB 8 KB       40 KB

name                 rows        reserved data   index_size unused
NONSPARSECOLUMNTABLE 25000       712 KB  656 KB 8 KB       48 KB
sparse SQL SERVER   2008   Introduction to SPARSE Columns


Script:
CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),FirstCol INT,SecondCol VARCHAR(100),ThirdCol SmallDateTime)GOCREATE TABLE Sparsed(ID INT IDENTITY(1,1),FirstCol INT SPARSE,SecondCol VARCHAR(100SPARSE,ThirdCol SmallDateTime SPARSE)GODECLARE @idx INT = 0WHILE @idx 50000BEGIN
INSERT INTO 
UnSparsed VALUES (NULL,NULL, NULL)INSERT INTO Sparsed VALUES (NULL, NULL, NULL)SET @idx+=1ENDGOsp_spaceused 'UnSparsed'GOsp_spaceused 'Sparsed'GODROP TABLE UnSparsed
GO
DROP TABLE Sparsed
GO