Find Created date and Modified date of Database objects

This script will return the objects in sql server database with modified and created date.
However you can change the order by clause as well as the Where clause according to your requirements

SELECT sys.schemas.name + '.' + sys.objects.name,
create_date,
modify_date
FROM sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE type = 'P'
OR type = 'U'
OR type = 'FN'
OR type = 'V'
ORDER BY modify_date DESC

The newsequentialid() built-in function can only be used in a DEFAULT expression

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.


This error occurs when we try to use the NEWSEQUENTIALID() function in the SQL statements or in SET statements. e.g.

SELECT NEWSEQUENTIALID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique
OR
INSERT INTO AA(id, name) VALUES(NEWSEQUENTIALID(),'abc')

The NEWSEQUENTIALID() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

SQL Server Interview Questions (Part 2)

Few days back, i went for an interview , following questions were asked

• How to Performance Tune a Stored Procedure?
• Type of Triggers?
• How to get the old value of a field from a trigger when updating a table?
• What is the difference between horizontal partition and vertical partition?
• if we have three parameters in the where condition (query has the joins with two or more tables table1.filed1=4 and table2.field2=5 and table3.field3=1)
  what will be the Order of execution of the where condition
  Which condition would be executed first i.e. field1 or field2 or field3?
• What are the disadvantages of the cursors?
• A stored procedure has cursors in it, how we can eliminate the cursors?
• What is the difference between temporary table and table variable?
• How to restore the Differential Backup?
• What are Backup Types and difference between them?
• What are Replication Types, What is snapshot replication?
• What is the difference between Replication and Mirroring?
• Can a sub report in Crystal Reports can have another sub report?
• What is log shipping?
• Should we normalize the Database on more than 3rd Normal form?

What is Parameter Sniffing, How it can affect the Performance, and What is the Solution

What is parameter sniffing? How it can affect the performance. What is solution of parameter sniffing?

Let us try to answer these questions,

Whenever a stored procedure is executed for the first time, its execution plan is created. An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL SERVER Database engine compiles a stored procedure it looks at the parameters being passed to the stored procedure and then creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is called "Parameter sniffing". Sometimes Parameter sniffing can lead to inefficient execution plans, especially when a stored procedure is called with parameter values that have different cardinality.

Parameter sniffing can be considered a plus to the performance of the system, but in those cases where it's not, various mechanisms can be used to avoid it. One of the simplest is to use local variables. You can also use the OPTIMIZE FOR query hint or the WITH RECOMPILE query hint. In extreme cases you can force specific execution plans onto procedures.

We can also say that Parameter sniffing is a process that occurs when executing a stored procedure for the first time that allows SQL Server to build an effective query plan. Normally that is a good thing, but in some cases can actually hurt performance of the query.

According to the white paper Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 Published in the Microsoft Site:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation"

When a SP is not in the procedure cache, when it is executed the query optimizer needs to compile the SP to create an execution plan. In order to do this the query optimizer needs to look at the parameters that are passed and the body of the SP to determine the best method to go about processing the SP. When the query optimizer looks at the SP's parameters, to help determine how to optimize the execution of the SP, it is known as parameter sniffing.

Before I show you with an example about parameter sniffing, you need to know that the query execution plan generated by the SQL Server depends on lot of factors; parameter sniffing is just one of them. So the execution plan I show here might be different from the execution plan that you will generate.

Let's look at the following code

USE AdventureWorks

GO

CREATE PROCEDURE GetCustOrders (@FirstCust int, @LastCust int)

AS

SELECT * FROM Sales.SalesOrderHeader

WHERE CustomerID between @FirstCust and @LastCust;

This procedure GetCustOrders accepts two parameters @FirstCust int, @LastCust int . These parameters are passed in the where clause of the query to get the customer orders. Now look at the execution plan of the procedure by execution this procedure by passing different parameters.

USE AdventureWorks

GO

DBCC FREEPROCCACHE

EXEC GetCustOrders 1, 1000

I had used the statement DBCC FREEPROCCACHE to clear the procedure cache so that query optimizer should make a new plan for this execution instead of using any existing one.

Look at the

Actual number of Rows 31465 and

Estimated number of Rows 31465

Here you can see that query optimizer performed a Clustered Index scan operation to get the records.

Now execute EXEC GetCustOrders 600,610 and see the execution plan

It uses the Clustered index scan for this operation as well, because this plan was present in the procedure cache.

Check the number of actual rows and estimated rows,

Actual number of Rows 31465

Estimated number of Rows 31465

Note the in both cases the actual and estimated number of rows are same.

Now clear the procedure cache and execute the procedure with smaller values

DBCC FREEPROCCACHE

EXEC GetCustOrders600,610

Exmaince the actual number of rows and estimated number of rows, they are different now, because optimizer created a new plane for this

Query optimizer performed the seek operation. This means that by passing the smaller range of values , you can get the Index Seek operation, depending on your execution. If you execute the procedure with smaller values first and then execute the procedure with larger values. Then you can get Index seek for both of the executions.

The first compile of the Procedure generates the execution plan depending on the the parameters passed to the procedure and that plan is kept in the procedure cache for use of future executions of that procedure.

How to eliminate parameter sniffing?

There are three methods to avoid parameter sniffing,

By using local variables in the procedure By using RECOMPILE Query Hint Query hint OPTIMIZE FOR

By using Local variables

Parameter sniffing can be disabled by using the local variables in the stored procedure.

CREATE PROCEDURE GetCustOrders (@FirstCustint, @LastCustint)

AS

DECLARE @FC int/td>

DECLARE @LC int

SET @FC = @FirstCust

SET @LC = @LastCust

SELECT * FROM Sales.SalesOrderHeader

WHERE CustomerID BETWEEN @FC AND @LC

You can create two local variables @FC AND @LC that takes the values from parameters of the stored procedure and then pass those values to the query. In this way the actual values of the parameters are no longer contained in the BETWEEN clause in the SELECT statement, instead only those local variables are present.

This method of removing the parameter sniffing problem doesn’t mean you will get a best plan for each execution of the SP

By using Recompile

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure

USEAdventureWorks

GO

CREATE PROCEDURE GetCustOrders(@FirstCust int,@LastCust int)

WITH RECOMPILE

AS

SELECT*FROMSales.SalesOrderHeader

WHERECustomerIDbetween@FirstCustand@LastCust ;

By using Optimize For

Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization

CREATE PROCEDUREGetCustOrders(@FirstCustint,@LastCustint)

WITH RECOMPILE

AS

SELECT*FROMSales.SalesOrderHeader

WHERECustomerID between @FirstCustand @LastCust

OPTION (OPTIMIZE FOR (@FirstCust UNKNOWN,@LastCust UNKNOWN));

go

You can read more about Query Hints from msdn

SQL Load Generator

A really nice tool for Load testing on SQL Server. SQL Load Generator is used to generate a load simulating several different users. It is is used to run multiple concurrent queries against SQL Server. The user can choose the number of concurrent queries to run, provide different queries, choose SQL or domain accounts, and provide application name settings.

read more or if you want to download SQL Load Generator you need to go on codeplex SQL Load Generator

Unable to start T-SQL Debugging. Could not attach to SQL Server Process

How to Enable TSQL Debugging in SQL SERVER ?


Click on the menu Debug >> Start Debugging
An error will be produced "Unable to start T-SQL Debugging. Could not attach to SQL Server Process"

Here is the solution to get rid of this error

Expand the SQL Sever Management Studio
Expand the Security

Right Click on the Logins
Click on New Login
A new window will appear to define the Login

Specify the Login Name.
Specify the Server roles.

Check the sysadmin Server role and then press OK.
Now Debug your Query /SP Debug >> Start Debugging

This can also be done using TSQL.
Here is the Way to do this using TSQL

sp_addsrvrolemember 'Domain\Name', 'sysadmin'
GO

EXEC master..sp_addsrvrolemember @loginame = N'Dmain-Name\Administrator', @rolename = N'sysadmin'
GO

if you are not using any domain, then try your system name.

SQL Server Performance Killers

There might be several individual factors that can kill the performance of your database. If you aware of the main performance killers in SQL Server in advance, you will be able to focus your tuning efforts on the likely causes.
You also need to look the hardware, operating and SQL Server settings
The main performance killers in SQL SERVER performance are as follows

• Poor Indexing
• Inaccurate statistics
• Excessive blocking and deadlocks
• No-set-based operations, usually T-SQL cursors
• Non set-based thinking leads to excessive use of cursors and loops rather than exploring more efficient joins and sub-queries. SQL has rich mechanism to get the data instead of trying to loop or row by row approach to be used in the code, this will kill the performance

• Poor query design
• Querying the data from a table that is not required, or using a table in joins that is not required.
• Poor database design
• No reusable execution plans
• Poor execution plans, usually caused by parameter sniffing
• Frequent recompilation of execution plans
• Improper use of cursors
• Always use set based tsql query. By using cursors, you add a large amount of overhead on SQL SERRVER.

• But if you are forced to use cursors, try to use the efficient cursor types such as fast-forward only.
• Remember cursors are called the performance killers.
• Improper configuration of database log
• Excessive use or improper use configuration of tempdb
• Every SQL Server instance has only one tempdb, which is used to store the information such as operations involving temporary tables, table variables, also operations such as sorts and row versioning, tempdb can become a bottleneck.