www.SQLProf.com

How to Attach a database without a transaction log file (.ldf)

Eric Vaillancourt — Thu, 17 Apr 2008 16:46:48 GMT

If you ever lose the drive that contains your log file, your database will become suspect and will stop working.

Notice that you cannot expand the database. If you do, you will get an error message.

You do not need to restore the backup in such a crash. If you do, you will lose everything since your last BACKUP. All you have to do is to detach the database and reattach it without the log. SQL Server 2005 does allow you to do this. You can reattach the database by following these simple steps:

first, you need to detach the suspect database:

The “detach database” dialog appears, click ok.

You might get an error just ignore it and the database will be detached. You will have to refresh the database list to remove your database from the list.

Right-click on Databases and select Attach

The “attach databse” dialog appears. Click on the “Add” button on locate your mdf file.

Under database details, select the .LDF file and click the Remove button.

By doing this, you are telling SQL Server to create a new log file.

Click OK. The database will be attached with a new logfile.

Another way would have been to use the CREATE DATABASE with the FOR ATTACH command:

USE [master]

CREATE DATABASE [Prod_db] ON

( FILENAME = N'C:\data\prod_db.mdf' )

FOR ATTACH

Hope this helps,

Eric Vaillancourt

www.sqlprof.com

Technorati : SQL server 2005,Attach Databse,Log file,.ldf,CRASH

Pivots with Dynamic Columns in SQL Server 2005/2008

Eric Vaillancourt — Sat, 12 Apr 2008 14:13:51 GMT

Pivots in SQL Server 2005/2008 can convert row into column data. Pivots are frequently used in reports, and are reasonably easy to work with.

However, many people have asked me how to make the column list dynamic. Normally, this list is fixed, but many times the new columns are determined by the data at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:

PIVOT allows you to turn data rows into columns. For example, if you have a query like this:

USE AdventureWorks

SELECT * FROM

(SELECT CustomerID, DATEPART(m, OrderDate) OrderMonth, SubTotal

FROM Sales.SalesOrderHeader

WHERE OrderDate between '20030101' and '20031231'

and CustomerID IN (2,4,6,7,8,9)) src

PIVOT (SUM(SubTotal) FOR OrderMonth

IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt

The rows coming from the internal query are transposed into colums:

SELECT * FROM

(SELECT CustomerID, DATEPART(m, OrderDate) OrderMonth, SubTotal

FROM Sales.SalesOrderHeader

WHERE OrderDate between '20030101' and '20031231'

and CustomerID IN (2,4,6,7,8,9)) src

PIVOT (SUM(SubTotal) FOR OrderMonth

IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt

This is ok if the colum list suppied in the IN clause is fixed like the 12 months of the year. But if the list evolves with the data, you would have to re-write your pivot. Not to practical.

Let’s look at another example:

SELECT * FROM

(SELECT CustomerID, YEAR(OrderDate) OrderYear, SubTotal

FROM Sales.SalesOrderHeader

WHERE CustomerID >=1 and CustomerID <=35) src

PIVOT (SUM(SubTotal) FOR OrderYear

IN ([2002],[2003])) AS pvt

In this pivot example, we want to display a colums for each year and a row for each customer:

Notice that we are only displaying data for years 2002 and 2003. We could modify our pivot to make it dynamic.

Look at the following code:

DECLARE @listCol VARCHAR(2000)

SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(str(YEAR(OrderDate)))

FROM Sales.SalesOrderHeader

ORDER BY '],[' + ltrim(str(YEAR(OrderDate)))

FOR XML PATH('')

), 1, 2, '') + ']'

By using the combination of STUFF and FOR XML PATH we can build a string that concatenates all columns by doing a SELECT DISTINCT on our table before doinf the pivot.

After executing this piece of code, you should get in the @listCol variable the list of colums needed to do the pivot.

Now, all we have to do is to convert our pivot into a dynamic query:

DECLARE @listCol VARCHAR(2000)

DECLARE @query VARCHAR(4000)

SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(str(YEAR(OrderDate)))

FROM Sales.SalesOrderHeader

ORDER BY '],[' + ltrim(str(YEAR(OrderDate)))

FOR XML PATH('')

), 1, 2, '') + ']'

SET @query =

'SELECT * FROM

(SELECT CustomerID, YEAR(OrderDate) OrderYear, SubTotal

FROM Sales.SalesOrderHeader

WHERE CustomerID >=1 and CustomerID <=35) src

PIVOT (SUM(SubTotal) FOR OrderYear

IN ('+@listCol+')) AS pvt'

EXECUTE (@query)

Et voila:

Hope this was helpful,

Eric Vaillancourt

www.sqlprof.com

Technorati : SQL Server 2005,Dynamic Select,PIVOT

How to Tune a Database Using the Database Tuning Advisor (DTA)

Eric Vaillancourt — Sat, 05 Apr 2008 13:51:14 GMT

The Database Engine Tuning Advisor (DTA) is a new tool in SQL Server 2005 that enables you to tune databases and improve the performances of your queries. DTA examines how queries are processed in the databases you specify and then it recommends how you can improve performance by suggesting the creation of indexes and statistics. It replaces the Index Tuning Wizard from Microsoft SQL Server 2000.

You can use DTA to tune a database by using workload (trace files) that were previously created with the profiler or you can tune a Query directly in Management Studio.

A workload is a set of Transact-SQL statements that execute against the database that you want to tune. The Database Engine Tuning Advisor use trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases.

In this article, we will create a few tables in the Adventureworks sample database. Run the following script:

USE AdventureWorks

SELECT * INTO DTA_Individual

FROM Sales.Individual

SELECT * INTO DTA_Customer

FROM Sales.Customer

SELECT * INTO DTA_CustomerAddress

FROM Sales.CustomerAddress

SELECT * INTO DTA_Address

FROM Person.Address

SELECT * INTO DTA_Vendor

FROM Purchasing.Vendor

SELECT * INTO DTA_VendorContact

FROM Purchasing.VendorContact

SELECT * INTO DTA_Employee

FROM HumanResources.Employee

SELECT * INTO DTA_SalesOrderHeader

FROM Sales.SalesOrderHeader

SELECT * INTO DTA_SalesOrderDetail

FROM Sales.SalesOrderDetail

SELECT * INTO DTA_Product

FROM Production.Product

Now we have tables to work with.

Creating a Trace file:

To run SQL Server Profiler, on the Start menu, point to All Programs, Microsoft SQL Server 2005, Performance Tools, and then click SQL Server Profiler. Or, you can to start the profiler from the tools menu in Management Studio.

Once the SQL Profiler is started, you need to create a new trace. “Select New Trace…” from the file menu:

After you have connected to the server, the Trace Properties window appears:

The DTA assumes that the workload trace file is a rollover file.

The captured workload information can be stored in a file (recommended for tuning) or in a table. I recommend that you use the SQL Server Profiler Tuning template for capturing workloads for DTA.

If you want to use your own template, ensure that the following trace events are captured for the version of SQL Server that you are using.

SQL Server 2005:

RPC:Completed

SQL:BatchCompleted

SP:StmtCompleted

SQL Server 2000:

RPC:Completed

SQL:BatchCompleted

The best time to create a trace file is when actual users are on the server doing real work. Not you running a few queries right and left. A workload should be representative of your typical load on the server. Starting the profiler WILL have an effect on the performance of the server. I also recommend increasing the max file size, since the profiler will create a new file once it reaches the limit. I normally increase it to 100MB.

Running the Trace:

Click on the run button and go back to Management Studio and run the following script:

USE AdventureWorks

SELECT City, NumberOfOrders = count(*)

FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'

JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID

JOIN DTA_Address a ON a.AddressID = ca.AddressID

JOIN DTA_SalesOrderHeader sh ON c.CustomerID = sh.CustomerID

JOIN DTA_SalesOrderDetail sd ON sd.SalesOrderID = sh.SalesOrderID

JOIN DTA_Product p ON p.ProductID = sd.ProductID

WHERE StateProvinceID = (SELECT StateProvinceID FROM Person.StateProvince WHERE StateProvinceCode = 'CA')

GROUP BY City

ORDER BY 2 DESC

SELECT i.CustomerID, c.ModifiedDate, City

FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'

JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID

JOIN DTA_Address a ON a.AddressID = ca.AddressID

WHERE i.CustomerID = 13311

SELECT i.CustomerID, c.TerritoryID, City

FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'

JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID

JOIN DTA_Address a ON a.AddressID = ca.AddressID

WHERE StateProvinceID = (SELECT StateProvinceID FROM Person.StateProvince WHERE StateProvinceCode = 'CA')

ORDER BY i.CustomerID, City

When you go back to the profiler, you will see everything that was sent to the server:

When you will be running this for real (on your production server), you have to let the trace run for a while; I let it run for 20-30 minutes. When you feel you have a good workload, you can stop the trace and quit the profiler.

Running the Database Tuning Advisor:

To begin, open the DTA, From the Windows Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Performance Tools, and then click Database Engine Tuning Advisor. In the Connect to Server dialog box, verify the default settings, and then click Connect.

By default, Database Engine Tuning Advisor opens to the configuration in the following illustration:

Before you can start the DTA, you need to select the trace file and select the database(s) to tune.

Click the Start Analysis button on the toolbar. While Database DTA is analyzing the workload, you can monitor the status on the Progress tab. When tuning is complete, the Recommendations tab is displayed.

After a couple of minutes (could take a while depending on the trace file size) the Recommendations tab is displayed and you can see the recommendations:

At this point, you have two choices; you can apply the recommendations directly or save them to a file. I personally save the recommendation to file because I like to see what suggestions were made and I try to understand them. You do this by clicking Save Recommendations on the Actions menu. You will have to execute the file in Management Studio to implement the changes.

For this demo, it is OK to apply them. If you apply the recommendation directly from the DTA, you will have more problems removing them afterwards.

What was created?

Once you have implemented the recommendations you can see what was created by looking in the Indexes and the Statistics level for a given table in the Object Explorer. All objects created by the DTA starts with “_dta”.

Cleaning up:

You can run this script to clean up our mess:

USE AdventureWorks

DROP TABLE DTA_Individual

DROP TABLE DTA_Customer

DROP TABLE DTA_CustomerAddress

DROP TABLE DTA_Address

DROP TABLE DTA_Vendor

DROP TABLE DTA_VendorContact

DROP TABLE DTA_Employee

DROP TABLE DTA_SalesOrderHeader

DROP TABLE DTA_SalesOrderDetail

DROP TABLE DTA_Product

Hope this was useful,

Eric Vaillancourt

http://www.sqlprof.com

Technorati : SQL Server 2005,Tuning,Profiler,Index,Statistics,Performance

Functions to Work with NULL Values

Eric Vaillancourt — Thu, 03 Apr 2008 04:25:00 GMT

Today I was asked what was the replacement for the IIF command in MS Access and how to manage nulls in SQL Server. So I decided to write about nulls, not that I like to work with them...but sometimes we don't have a choice ;)

SQL Server may generate unexpected results when performing calculations on columns that have NULL values. So, to resolve this problem you need to use the ISNULL, NULLIF, and COALESCE functions of SQL Server 2005 when your columns hold NULL values.

ISNULL: The ISNULL function replaces NULL values with the specified replacement value. The following is the syntax of the ISNULL function:

ISNULL ( check_expression , replacement_value )

The function takes the following arguments:

check_expression is the expression to be checked for NULL values and can be of any data type.

replacement_value is the expression to be returned if check_expression is NULL and should be of the same data type as the check_expression.

The following code example demonstrates the use of the ISNULL function in a SELECT statement.

USE Adventureworks;

SELECT AVG(ISNULL(Weight, 60))

FROM Production.Product

In this example, the ISNULL function is used to find the average of the weight of all products. The ISNULL function substitutes the value 60 for all NULL entries in the Weight column of the Product table.

NULLIF: The NULLIF function returns a NULL value if the two specified expressions are equivalent. The following is the syntax of the NULLIF function:

NULLIF (expr , expr)

The function takes two arguments. The argument, expression, can be a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators. The NULLIF function returns the result that is of the same data type as the first expression.

The NULLIF function returns the first expression if the two expressions are not equivalent. Otherwise, it returns a NULL value.

The following code example retrieves the average StandardCost of products that belong to the Class ‘M’.

USE Adventureworks;

SELECT AVG (StandardCost) AS AvgCost

FROM Production.Product

WHERE Class='M'

This query returns an average of 378.5403 that includes some rows that have NULL values in the StandardCost column. Therefore, the average cost value that is displayed in the result set is incorrect, it should have been 402.199. However, the same query can be rewritten with the help of the NULLIF function to ensure that the products with NULL as StandardCost are not included in the calculation.

USE Adventureworks;

SELECT AVG(NULLIF(StandardCost, 0)) AS AvgCost

FROM Production.Product

WHERE Class='M'

COALESCE: The COALESCE function returns the first non-null expression among its arguments. The following is the syntax of the COALESCE function:

COALESCE ( expression [ ,...n ] )

The function takes two arguments. The argument expression is an expression of any data type. The argument n is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same data type.

The COALESCE function returns the result that is of the same data type as the expression. If all arguments of the COALESCE function are NULL, then it returns NULL.

The ISNULL function replaces NULL values with the specified replacement value. The following is the syntax of the ISNULL function:

The following example uses the COALESCE function to return the first non-null telephone number for a particular customer and for customers with no valid telephone numbers, the string 'No number on file' is displayed.

SELECT CustomerName, COALESCE(CellPhone, OfficePhone, HomePhone, 'No number on file') as Telephone FROM Customer

Hope this helps,

Eric Vaillancourt

http://www.sqlprof.com

Technorati : COALESCE,NULLIF,ISNULL

How to Use Aggregate Functions with NULL Values

Eric Vaillancourt — Thu, 03 Apr 2008 00:49:21 GMT

Effect of NULL Values on Aggregate Functions

NULL values may cause aggregate functions to produce unexpected or incorrect results because these functions ignore NULL values for calculations. For example, if the Weight column of a table has 504 records, of which 299 records have NULL values, the average of this column calculated by using the AVG function may return incorrect values.

The following code example demonstrates this:

USE AdventureWorks

SELECT AVG(Weight) AS 'Average Weight'

FROM Production.Product

When we execute the query, we get an average weight of 74.069219. We get this result because when calculating the average, the AVG function considers only the 205 rows that contain non-NULL values. However, it is expected that the function considers all the 504 records.

The ISNULL Function to the rescue

You can correct this problem by using the ISNULL function, as shown in the following code example:

SELECT AVG(ISNULL(Weight,0)) AS 'Average Weight'

FROM Production.Product

Now we get the right result of 30.127361.

Effect of NULL Values on the COUNT(*) Function

The COUNT(*) function is an exception to this problem because it returns the total number of records in a table, irrespective of the NULL values, as shown in the following code example:

SELECT COUNT(*) AS 'COUNT'

FROM Production.Product

When we execute this query, we get the right count of 504.

Hope this helps,

Eric Vaillancourt

http://www.sqlprof.com

Technorati : ISNULL,Aggregate Functions,Count (*),AVG,SQL Server 2005

How to combine multiple rows of data on same line.

Eric Vaillancourt — Mon, 31 Mar 2008 18:41:37 GMT

I’m often ask how combine information on same line in a query. Let me explain, lets say that you would like to list all products from the Production.product table in the AdventureWorks database and that you would like to list ALL vendors for each product from the Purchasing.Vendor table on the same line.

Look at the following example:

You can see in the 3rd column that all venders are list separated by a comma.

In the AdventureWorks database the relation between the Product table and the Vendor table in done through a third table called ProductVendor. Look at the following diagram:

Let’s start with this simple query that uses inner joins to link all three tables:

SELECT Production.Product.Name AS [Product Name],

Production.Product.ProductNumber,

Purchasing.Vendor.Name AS Vendor

FROM Production.Product INNER JOIN Purchasing.ProductVendor

ON Production.Product.ProductID = Purchasing.ProductVendor.ProductID

INNER JOIN Purchasing.Vendor

ON Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID

ORDER BY [Product Name]

When you execute this query, you get the following:

In this example, we get all vendors on separate rows. Not exactly what we want. To solve this issue, we need to create a user defined function that will return all of our rows on a single line.

Execute the script below:

CREATE FUNCTION [dbo].[ufnVendorOnSameLine](@Id int,@Delimiter varchar(10))

RETURNS nvarchar(max)

BEGIN

DECLARE @OnSameLine varchar(max)

set @OnSameLine=''

--by using SELECT to set the variable, all rows are concatenated together.

SELECT @OnSameLine = @OnSameLine + coalesce (Purchasing.Vendor.Name,'')+@Delimiter

FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor

ON Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID

WHERE (Purchasing.ProductVendor.ProductID = @Id)

--we need to remove the last delimiter

set @OnSameLine = substring(@OnSameLine,1,len(@OnSameLine)-1)

RETURN @OnSameLine

END;

Once we have created the udf, we need to modify the query to remove the Purchasing.Vendor.Name column and replace it with the function. Also, we need to include a DISTINCT since we are unse an inner join to the Purchasing.ProductVendor table and finally, we remove the inner join to the Purchasing.Vendor table:

SELECT DISTINCT Production.Product.Name AS [Product Name],

Production.Product.ProductNumber,

dbo.ufnVendorOnSameLine(ProductVendor.ProductID,', ') AS Vendors

FROM Production.Product INNER JOIN Purchasing.ProductVendor

ON Production.Product.ProductID = Purchasing.ProductVendor.ProductID

ORDER BY [Product Name]

How it Works:

Basicaly, we use a technique that has been around since the early day of MS SQL Sever. In fact prior to version 7, the SET command did not exist. We needed to use the SELECT to assign a value to a variable. For example:

SELECT @OnSameLine = @OnSameLine + coalesce (Purchasing.Vendor.Name,'')+@Delimiter

and

SET @OnSameLine = @OnSameLine + coalesce (Purchasing.Vendor.Name,'')+@Delimiter

Are the same…it’s just a long forgotten technique.

Hope this was useful,

Eric Vaillancourt

Technorati : SQL Server 2005,Reporting,Select

Avoid using local variables in your Select statements

Eric Vaillancourt — Sat, 29 Mar 2008 18:36:04 GMT

People are always surprised when I tell that their queries will run slower when they use local variables in their queries. Let me explain: If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a guesstimate for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer will do a better selecting a query plan. For example, execute both of these queries. The first one uses a local variable and the second one a literal:

use adventureworks

--first one with a local varaiable

declare @StartOrderDate datetime

set @StartOrderDate = '20040731'

select * from Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl

WHERE hdr.SalesOrderID = dtl.SalesOrderId

AND hdr.OrderDate >= @StartOrderDate

--secondeone with a literal

SELECT * FROM Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl

WHERE hdr.SalesOrderID = dtl.SalesOrderId

AND hdr.OrderDate >= '20040731'

The number of rows from Sales.SalesOrderHeader that the optimizer estimates will qualify vs. the condition hdr.OrderDate >= @StartOrderDate is 9439.5, which is exactly 30% of the size of the table. (the observations that follow are based on my SQL Server 2005 version; your results may differ depending on your SQL Server version, available memory, etc.).

The cardinality of the result set for the predicate "hdr.OrderDate >= '20040731'" is estimated as 40 for a selectivity of 0.13%. The plan chosen for this query uses a nested loop join instead of a merge join because of this improved estimate.

To force SQL Server to optimize a query every time it is run, and use the values of local variables to estimate cardinality and cost during optimization of the query, add the RECOMPILE query hint to the query. For example, modify the first of the two preceding example queries shown to:

SELECT * FROM Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl

WHERE hdr.SalesOrderID = dtl.SalesOrderId

AND hdr.OrderDate >= @StartOrderDate

OPTION(RECOMPILE)

This eliminates the problem of bad estimates due to local variables, at the cost of recompiling the query every time you run it. Compilation overhead can be quite high, especially for frequently-run, small queries. To avoid this overhead, further modify your application to use parameterized batches or stored procedures instead of dynamic SQL.

To eliminate the use of local variables, consider :

Rewriting the query to use literals directly in queries instead of assigning them to variables and then using the variables in the queries;
Using sp_executesql or API server cursors with parameters that replace your use of local variables;
Using a stored procedure with parameters that replace your use of local variables.

Hope this helps,

Eric Vaillancourt

Technorati : SQL Server 2005,Tuning,Statistics,Performance

Reference : http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

Preventing Microsoft Excel or any other application from connecting to SQL Server 2005

Eric Vaillancourt — Sat, 29 Mar 2008 03:32:38 GMT

Today, I was asked a question on how to prevent Microsoft Excel from connecting to SQL Server. At first I wasn’t sure how to respond to this, but I finally found a way. It is a simple trick that uses the FOR LOGON option now available since the SP2 release of SQL Server 2005.

USE master

CREATE TRIGGER Prevent_Apps_logon

ON ALL SERVER FOR LOGON

BEGIN

IF APP_NAME() LIKE '%excel%' -- or any other app name!

ROLLBACK

END

Hope this helps,

Eric

Technorati : SQL Server 2005,DDL Triggers,FOR LOGON,Security

Auditing Logins in SQL Server 2005

Eric Vaillancourt — Sat, 29 Mar 2008 03:14:52 GMT

When I teach my SQL administration classes, I'm often ask if SQL Server provides a mechanism to log user logins. Before SQL Server 2005 SP2, this task was possible but not reliable. In SQL Server 2005 this task is easy to implement using DDL triggers (Data Definition Language).

One reason that people ask me this question, is because they need to comply with Sarbanes-Oxley. I will be writing a series of articles on auditing for SOX in the next weeks.

As a consultant, I am frequently invited to attend meetings with Sarbanes-Oxley auditors to discuss the security and integrity of corporate data. they want to know who has access to the data, how access is granted, and how is the company monitoring to prevent someone from sneaking in, logging on, and doing something they shouldn't be doing.

In SQL 2005, a trigger can be created in order to perform any action upon a DDL statement. The scope can be database level or Server level. DDL triggers allow us to track critical changes to our database environment that may be intentional, by mistake, or malicious.

When you design DDL triggers, it's important to determine what events you want to audit, and to determine in which scope each event occurs. In this article, we will write a trigger to capture logins, which are server level events. You can get a list of all events that are valid for use in DDL triggers.

The first step is to create a database and a table that we will use to store the logs.

Execute the following code:

use master

create database Admin_Log

use Admin_Log

create schema [admin]

CREATE TABLE admin.logs (

LogID int IDENTITY(1,1),

EventTime DATETIME,

EventType VARCHAR(100),

LoginName VARCHAR(100),

HostName VARCHAR(100),

AppName VARCHAR(255),

Event_Data XML)

Note that the table makes use of the XML data type, which is new in SQL Server 2005. As you'd assume by its name, its job is to hold XML data.

Once your table is in place to keep track of the events, you're ready to create the trigger necessary to watch for these event.

Execute the following code:

use master

create trigger ServerWideLoginLogs

on all server

with execute as self

for LOGON

as begin

DECLARE @event XML

SET @event = eventdata()

INSERT INTO Admin_Log.admin.logs (EventTime,EventType,LoginName,HostName,AppName,Event_Data)

VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)),

APP_NAME(),

@event)

end

This script creates the trigger that declares that we want to look for all DDL Login events that occur on the server. The information resulting when this trigger fires will go into the admin.logs table that we created earlier. Notice that we casting information from our @event variable (that we initialized using the eventdata() function) to insert data into our table.

In this context, the eventdata() function will gather information about the triggered event (LOGON) because that's the event specified in the statement FOR.

To test our new trigger, try connecting to the server and execute the following code:

select * from admin.logs

After running this statement, you will see that a record is inserted every time someone connects to the server.

What's in your XML?
The Event_Data field is populated by the EVENTDATA() function from our INSERT statement in our trigger. In this case, the eventdata() function returns XML data related to the connection, including the date and time of the login, the server name, the user ID, and the machine system ID.

Hope this was helpful,

Eric Vaillancourt

Technorati : DDL Triggers,Sarbanes-Oxley,Audit,SQL Server 2005

Understanding SET STATISTICS IO and SET STATISTICS TIME

Eric Vaillancourt — Sun, 23 Mar 2008 14:15:07 GMT

Today, I want to talk about two commands that are often overlooked. SET STATISTICS IO and SET STATISTICS TIME these two commands are very useful when comes time to tune a query.

Tuning a query seems simple enough. In essence, we want our searches to run faster. When I teach my class on “Optimizing SQL Server” I realize that many people find it difficult to accomplish this task. While there are many reasons why query tuning is difficult, this article will concentrate on one aspect. And that is that query tuning takes place in an environment that is often changing from second to second, making it hard to really know what exactly is going on.

When you run a query, different server resources are used. One of those resources is the CPU time. If you run the same query over and over again, the CPU time needed to run the query will remain very close from one execution to other. I am not talking about the total time it takes to run query from the start to the end, but the amount of CPU time needed to run the query.

Another resource that the Server needs is IO. Whenever you run a query, the server must retrieve data from memory (logical reads) or from the disk (physical reads). Using high CPU and IO resources will result in slow query performance.

So your goal should be to rewrite your queries in such a way that they use less CPU and IO resources. If you accomplish this, then the performance of your query will improve.

Both the SET STATISTICS IO and the SET STATISTICS TIME Transact-SQL commands have been around a long time. But for some reason, they are often ignored.

Both commands can be “turned on” using Transact-SQL commands or by setting the appropriate connection properties in Options dialog from the tools menu in Management Studio.

By default, these settings are not turned on. The SET STATISTICS IO and the SET STATISTICS TIME commands act like settings, turning on and off various reported information on the resources our queries use.

So let’s look at an example of how use these commands.

To begin our example, start Management Studio and connect to a SQL Server. For this example, we will use the sample database provided by with SQL Server 2005 and that is AdventureWorks. So start by making AdventureWorks your default database.

Next, run this query:

USE AdventureWorks;

SELECT * FROM Person.Contact;

Normally this query should return 19972 rows. Now, let’s run the same query, but this time, let's run the SET STATISTICS IO and the SET STATISTICS TIME commands before we run the query. Before we begin our example, run these two commands DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE (don’t run these on a production server) first. These two commands will clear out SQL Server’s data and procedure cache.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Now, enter and run the following Transact-SQL commands:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Once you have done this, then run this command again:

SELECT * FROM Person.Contact;

After you run these command, you will see new information in the message tab that you did not see before.

(The results you get may vary, but we will talk about this below as we explain everything you see above.)

Let me explain what it all means:

SET STATISTICS TIME Results:

“SQL Server parse and compile time:” tells us how much CPU time and total time it took SQL Server to parse and compile our T-SQL statement. In my case, it took 0 ms. of CPU time, and a total time of 359 ms. of elapsed time. Your CPU time will probably vary because your server is different than mine.

If you re-execute the query, again without clearing the cache (as recommended above), most likely both instances of CPU and compile time will be 0, as the cached query plan will be reused, so no new compile time is required.

This is not too important, what is of interest to us is the time information displayed at the bottom of the query’s output. Here is it is again:

SQL Server Execution Times:

CPU time = 125 ms, elapsed time = 1112 ms.

What this tells us is how much CPU time was used to execute the query, and how long the query took to run. The CPU time is a somewhat consistent measurement of the amount of CPU resources it takes for your query to run, and this is relatively independent of how busy your CPU is.

The elapsed time number is a measurement of how long the query took to execute (not counting the time for locks or reads). This number will vary a lot because depending on the load on your server.

Because the CPU time is relatively consistent, this is what you should use to determine whether the changes you make in your queries, are helping or making things worse.

SET STATISTICS IO Results:

Table 'Contact'. Scan count 1, logical reads 561, physical reads 1, read-ahead reads 559, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is not all useful; let me try to explain what it means.

Output item	Meaning
Table	Name of the table, in our case ‘Contact’
Scan count	Refers to the number of times that tables referenced in the query have been accessed. In our example, the table in our query was only accessed 1 time. For queries that don’t include joins, this information is not too helpful. But if your query has one or more joins, then this information may be of use to you. You may want to pay attention to the Scan Count, watching to see if it is increasing or decreasing as you tune the query.
logical reads	Number of pages read from the data cache. This is the most useful piece of data provided by the SET STATISTICS IO commands. As you probably know, before SQL Server can do anything with data, that data must be in its data cache. In addition, when SQL Server reads data from the data cache, it reads it in 8K pages. A logical read is the number of pages SQL Server had to read from the data cache in order to produce the results specified by our query. The exact same number of logical reads will always occur when the same query runs against the exact same data on SQL Server. Knowing the number of logical reads that SQL Server takes to execute a query is important because it is the one thing that never changes from one execution to another giving us the perfect reference to use when we are tuning a query to determine if you we are succeeding or failing. The fewer the logical reads performed by a query, the more efficient it is, and the faster it will perform, assuming all other things are held equal.
physical reads	Number of pages read from disk. This information is not too useful for query tuning. First of all, a physical read refers to when SQL Server must go to disk to get data pages it needs to put into the data cache before it can execute the query. As mentioned above, all data must be in the data cache before SQL Server can execute a query. Whenever SQL Server begins to execute a query, the first thing it does is to check and see if the data pages it needs are in the data cache.
read-ahead reads	Number of pages placed into the cache for the query. Like physical reads, this information is not too useful for query tuning. This value tells you the number of physical page reads that SQL Server performed as part of its read-ahead mechanism. To help optimize its performance, SQL Server reads physical data pages ahead of when it thinks your queries might need the data.
lob logical reads	Number of text, ntext, image, or large value type (varchar(max), nvarchar(max), varbinary(max)) pages read from the data cache.
lob physical reads	Number of text, ntext, image or large value type pages read from disk.
lob read-ahead reads	Number of text, ntext, image or large value type pages placed into the cache for the query.

Now for a real example:

To be honest with you, when you execute a query without a WHERE clause there’s not much you can do to tune a query because the server performs a scan on the table. Let’s take another example:

Execute the following code:

USE AdventureWorks;

SELECT * FROM Person.Contact WHERE firstname='Carla';

Now look at the Message tab:

Even though we have filtered our query and the elapsed time is down (data is cached), the logical reads are still at 561. This means that reading 25 rows is costing us the same as in the previous example that was returning 19972 rows.

One thing we could do to reduce the number of logical reads is to create an index on firstname (I will cover the creation of index in another article).

You can create an index directly in Management Studio by selecting the table in the object explorer and right clicking on the Indexes folder:

A new index dialog will appear:

Or you can execute the following code:

USE [AdventureWorks]

CREATE NONCLUSTERED INDEX [IDX_firstname] ON [Person].[Contact]

(

[FirstName] ASC

)

Either way, you will achieve the same result. Now re-execute the query and look at the message tab:

Now that is quite an improvement…

Hope this was useful, feel free to post any comments.

Eric

Technorati : SET STATISTICS IO,SET STATISTICS TIME,Tuning,SQL Server,Performance

Scripting Multiple Objects

Eric Vaillancourt — Wed, 19 Mar 2008 23:57:34 GMT

The other day I found a way to script multiple objects using the "Object Explorer Detail" tab in Management Studio. To be honest, I never thought that this tab was useful.

All you have to do is select the type object you want to script in the object explorer and do a multiple selection in the Object Explorer Detail window.

Look at the example below:

Hope this helps,

Eric