Home » Archives for November 2012

November 2012

sql server DBA 999

Parameter Sniffing in Stored Procedures in SQL Server 2008/2008 R2

Parameter Sniffing in Stored Procedures in SQL Server 2005/2008/2008 R2
Today let me cover one of a good topic on Parameter Sniffing in Stored Procedures:

Paramater Sniffing is not a unexpected behaviour when passing parameters into Store Procedure.


CREATE procedure [dbo].[Usp_Test_SP]
@Category as [nvarchar](60)=null
as
select Category ,Description,WorkOrder,Date Added from TestTable
where (sc.session_class_desc like @Category +'%' or @Category is null)

Say about SP causes a index seek + bookmark lookup then this compiled plan will be cached and reused for subsequent executions of the procedure.

But when sometime if rows returned are very high like 30% of Table rows then compiled plan may result in Full table scan and this is stored for future use.… Read the rest

sql server DBA 999

Automating SFTP Downloads using WinScp Free Tool

Today’s article is about how to Automating SFTP Downloads using WinScp Free Tool:

This will be helpfull in many situations of which one is if you have your database hosted in Cloud by Hosted Services then for running reports etc. linking this database to another which is hosted locally, would mean you will need to automate SFTP downloads for that database and run restore script to Restore Database Automatically.

Procedure:

1. Install the WinScp Free Tool.
2. Access your FTP Site using SFTP(I used implicit FTP). If you cant access this then check your Firewall and see if this is blocked. Add exceptions to Firewall as needed.
3. Store a Session inside WinScp with Login and password credentials for Automating … Read the rest

sql server DBA 999

Adding Data Collectors on SQL Server 2008/2008 R2/2012/2014/2016

Adding SQL Server Data Collectors to SQL Server is very important for Troublshooting issues and Root cause analysis of many issues normally occuring on SQL Servers.

These counters are helpful for both Virtual or Physical SQL Servers. They are available in all versions like sql server 2008 data collector, sql server 2012 data collector, sql server 2014 data collector

    The Counters related to SQL Instance which are really important are:


SQLServer:Access MethodsFreeSpace Scans/sec
SQLServer:Access MethodsFull Scans/sec
SQLServer:Access MethodsIndex Searches/sec
SQLServer:Access MethodsPages Allocated/sec
SQLServer:Access MethodsWorkfiles Created/sec
SQLServer:Access MethodsWorktables Created/sec
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerCheckpoint pages/sec
SQLServer:Buffer ManagerDatabase pages
SQLServer:Buffer ManagerFree list stalls/sec
SQLServer:Buffer ManagerFree pages
SQLServer:Buffer ManagerLazy writes/sec
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Buffer ManagerReadahead pages/sec
SQLServer:Buffer ManagerReserved pages
SQLServer:Buffer … Read the rest