SQL Server

ISRO unveils mapping application 'Bhuvan'

2009-08-12T10:41:00.000-07:00

ISRO will unveil its version of Google Earth, the iconic mapping service for the common man to zoom into any part of the world on their personal computer using satellite images.

However, the new mapping service -- Bhuvan, which means earth in Sanskrit -- will allow users to have a closer look at any part of the subcontinent barring sensitive locations such as military and nuclear installations.

ISRO Chairman G Madhavan Nair will unveil the Bhuvan webportal at a function here tomorrow in the presence of Minister of State in the PMO Prithviraj Chavan.

Bhuvan will use images taken by ISRO's seven remote sensing satellites at least a year ago. These eyes in the sky can capture images of objects as small as a car on the road to build three-dimensional map of the world.

http://bhuvan2.nrsc.gov.in

Google Chrome Operating System

2009-07-09T11:01:00.000-07:00

Google Chrome has always been a little more than a browser: it's optimized for running web applications, each tab runs as a separate process, the interface is minimalistic and there's even a task manager. "We realized that the web had evolved from mainly simple text pages to rich, interactive applications and that we needed to completely rethink the browser. What we really needed was not just a browser, but also a modern platform for web pages and applications, and that's what we set out to build," said Google in September 2008.

Google's blog announces a natural extension of the Chrome project: an operating system for netbooks. "Google Chrome OS is an open source, lightweight operating system that will initially be targeted at netbooks. Later this year we will open-source its code, and netbooks running Google Chrome OS will be available for consumers in the second half of 2010. (...) Google Chrome OS will run on both x86 as well as ARM chips and we are working with multiple OEMs to bring a number of netbooks to market next year. The software architecture is simple — Google Chrome running within a new windowing system on top of a Linux kernel."

As people use more and more web applications, the operating system becomes less important and it makes no sense to pay for it. The desktop mail client could be replaced by Gmail, the calendaring application could be replaced by Google Calendar, the office suite has lightweight alternatives: Google Docs and Zoho, it makes more sense to use an online feed reader like Google Reader, your scientific calculator is less powerful than Wolfram Alpha and you'll rarely need a video player when you have YouTube, Hulu and other video sites.

This idea is not new and there are already operating systems optimized for the browser. For example, Good OS announced last year Cloud, an operating system that "integrates a web browser with a compressed Linux operating system kernel for immediate access to Internet, integration of browser and rich client applications, and full control of the computer from inside the browser". If Google manages to create a great user interface, the new operating system could be very successful.

http://googleblog.blogspot.com/2009/07/introducing-google-chrome-os.html

Gmail in 5 Indian Languages Hindi, Tamil, Telugu, Kannada and Malayalam

2009-03-31T12:20:00.000-07:00

It's hard for me to imagine going without email for a day. It's such an easy and convenient way to communicate with my friends and family. However, there was one limitation that bothered me: my family members and friends who prefer to communicate in Hindi did not have an easy way to type and send email in their language of choice. I am extremely happy to announce the launch of a new feature in Gmail that makes it easy to type email in Indian languages.

When you compose a new mail in Gmail, you should now see an icon with an Indian character, as the screenshot below shows. This feature is enabled by default for Gmail users in India. If you do not see this function enabled by default, you will need to go the "Settings" page and enable this option in the "Language" section.

When you click the Indian languages icon, you can type words the way they sound in English and Gmail will automatically convert the word to its Indian local language equivalent. For example, if a Hindi speaker types "namaste" we will transliterate this to "नमस्ते." Similarly, "vanakkam" in Tamil will become "வணக்கம்." We currently support five Indian languages -- Hindi, Tamil, Telugu, Kannada and Malayalam -- and you can select the language of your choice from the drop-down list next to the icon.

They built this new feature using Google's transliteration technology, which is also available on Google India Labs, Orkut, Blogger and iGoogle. I hope you find this feature useful to communicate with those of your friends and family who prefer to write in their native language, and it will be available soon to businesses and schools using Google Apps. Now back to replying to all those Hindi emails I got from my family and friends today!

How to Minimize SQL Server Blocking

2009-03-29T08:53:00.000-07:00

By default, blocking locks do not time out. The waiting connection waits until the lock is released, and the block is over. If you like, you can set a lock time-out so that a connection does not wait indefinitely for the blocking lock to be released. This is accomplished using the LOCK_TIMEOUT setting.

When the LOCK_TIMEOUT setting is used to set a maximum amount of time that a connection can wait for a blocking lock to go away, the connection with the lock that is causing the blocking problem is not affected, but the connection waiting for the block is halted and an error message is received. When this happens, error message 1222, "Lock request time-out period exceeded," is sent to the application.

This means that the application needs to include the appropriate error-handling code to deal with this situation and take the appropriate action, which includes rolling back the transaction. If the application does not know how to deal with this error message, and the transaction is not rolled back, it is possible that the application can continue as if the transaction was not automatically cancelled. Because of this, you should not use the LOCK-TIMEOUT setting unless your application(s) that will be affected by it know what to do when they receive this message from SQL Server.

The syntax for the SET LOCK_TIMEOUT is:

SET LOCK_TIMEOUT timeout_period

Where timeout_period is the number of milliseconds that a connection waits for a blocking lock to go away before an error is returned from SQL Server to the application. A value of -1 is the default, which means to wait indefinitely. A value of 0 tells SQL Server not to wait at all, and to return the error immediately.

This command is based on a per connection basis, and stays with the connection until the connection is broken, or a new SET LOCK_TIMEOUT command is issued. [7.0, 2000]

A view runs on our development server in 30 seconds, but it takes over 10 minutes on the production server. Why?

2009-03-29T08:42:00.000-07:00

ProblemThere are two SQL Server servers, both with virtually the same hardware. One is a development server, where we create and test Transact-SQL code, and the other is production SQL Server. Here's my problem. When a particular view runs on the development server, it takes about 30 seconds. But when the identical view on the production server, the view takes over 10 minutes to run. The databases on both servers are almost identical. Periodically copy the production database to the development server so that we are working with the same data and the same indexes. What could be causing the difference in time for this particular view to run?

Solution
Assuming that the servers and the databases are virtually the same, and the statistics have been updated on both of them, here's what is suggestted you to check. First, do an Execution Plan of the view that is causing the problem on the development server. Find out if this particular view is using parallelism as part of the execution plan. I am guessing that this is the case.

Now, do an Execution Plan of the view on the production server. Find out if this particular view is using parallelism as part of the execution plan. My guess is that it is not.

If I am write so far, then check the SQL Server Parallelism setting for each server. I bet that it is set to "Use All Available CPUs" on the development server, and set to use only one CPU on the production server. If I am right, then set the production server to "Use All Available CPUs," and the performance difference of the same view on both servers should be resolved, and performance should be virtually identical on both servers.

By default, SQL Server is set to use "Use All Available CPUs" on a server. If this is not your current setting, then this has been changed by someone.

Essentially, this setting tells SQL Server whether to attempt to execute queries in parallel using multiple processors. If the default "Use All Available CPUs" option is selected, then SQL Server will attempt to run queries in parallel, using multiple CPUs. This can sometimes result in dramatic performance differences in a query's performance. But if this option is turned off, then SQL Server will only use one CPU to execute a query, which can be much slower.

Keep in mind that if SQL Server is set to use parallelism, that it may not always use it. If the server is very busy, it may not be used, but when the server is less busy, it may be used. This means that sometimes the view will be fast, and other times, slow (depending if parallelism is being used or not).

On the other hand, sometimes parallelism can cause queries to take longer to execute than if parallelism is not used. In these cases, you can use a Query Hint to turn off parallelism for the odd-acting query.

SQL Server Transact-SQL DML

2009-03-29T08:40:00.000-07:00

Generally, it is better to perform multiple UPDATEs on records in one fell swoop (using one query), instead of running the UPDATE statement multiple times (using multiple queries).

For example, you could accomplish this two different ways:

USE Northwind
UPDATE Products
SET UnitPrice = UnitPrice * 1.06
WHERE UnitPrice > 5

GO

USE Northwind
UPDATE Products
SET UnitPrice = ROUND(UnitPrice, 2)
WHERE UnitPrice > 5

GO

Or

USE Northwind
UPDATE Products
SET UnitPrice = ROUND(UnitPrice * 1.06, 2)
WHERE UnitPrice > 5

GO

As is obvious from this example, the first option requires two queries to accomplish the same task as the second query. Running one query instead of two or more usually produces the best performance. [6.5, 7.0, 2000, 2005]

How many logical and physical processors do you have?

2009-03-25T10:20:00.000-07:00

SELECT cpu_count AS [Logical CPUs],
cpu_count / hyperthread_ratio AS [Physical CPUs]
FROM sys.dm_os_sys_info

Sorting different dateformats correctly

2009-03-25T10:15:00.000-07:00

DECLARE @Stats TABLE
(
SomeDate DATETIME
)

INSERT @Stats
SELECT 20000 + ABS(CHECKSUM(NEWID())) % 30000
FROM master..spt_values

DECLARE @Style INT

SET @Style = 100

WHILE @Style <= 113
BEGIN
-- Orders by ISO format but displays according to @Style parameter
SELECT TOP 10 @Style AS Style,
CONVERT(VARCHAR(40), SomeDate, @Style) as SomeDate
FROM @Stats
GROUP BY CONVERT(VARCHAR(40), SomeDate, @Style),
CONVERT(VARCHAR(8), SomeDate, 112)
ORDER BY CONVERT(VARCHAR(8), SomeDate, 112) DESC

SET @Style = @Style + 1
END

The general idea is to group by both ISO format and the style you wish and the sort by the ISO format and display the other format.
The reason this work is that the two lines in GROUP BY clause are deterministic.

Performance Tuning for SQL Server Backup and Restore

2009-03-25T10:04:00.000-07:00

Depending on the size of your database, select the backup method that is the best tradeoff between backup and restore time. For example, full backups take the longest to perform, but are the fastest to restore. Differential backups are overall faster than full backups, but take longer to restore. Incremental (transaction log) backups are the fastest, but are generally the slowest to restore. [7.0, 2000, 2005]

*****

If you are backing up your databases directly over a network (not really recommended as it hurts performance), one way to boost throughput is to perform the backup over a dedicated network which is devoted to backups and restores only. All devices should be on the same high-speed switch. Avoid going over a router when backing up over a network, as they can greatly slow down backup speed. [6.5, 7.0, 2000, 2005]

SQL Server Transact-SQL DML

2009-03-13T03:02:00.000-07:00

Performing UPDATES takes extra resources for SQL Server to perform. When performing an UPDATE, try to do as many of the following recommendations as you can in order to reduce the amount of resources required to perform the UPDATE. The more of the following suggestions you can do, the faster the UPDATE will perform.
If you are UPDATing a column of a row that has a unique index, try to update only one row at a time.

Try not to change the value of a column that is also the primary key.

When updating VARCHAR columns try to replace the contents with contents of the same length.

Try to minimize the UPDATing of tables that have UPDATE triggers.

Try to avoid UPDATing columns that will be replicated to other databases.

Try to avoid UPDATing heavily indexed columns.

Try to avoid UPDATing a column that has a reference in the WHERE clause to the column being updated.

Of course, you may have very little choice when UPDATing your data, but at least give the above suggestions a thought.

How to Perform Multiple lookups to the same table

2009-03-12T07:45:00.001-07:00

In a SQL Server Integration Service (SSIS) package, when lookups are performed on the tables which are inserted in the same package by a previous task, the error “could not match” is raised.

See the failed package layout. From the insert data, two records will be inserted and at the lookup those values are used to get the id. However, the package fails indicating that records are not found even though those records are in the table.

This is happening due to the cache setting in the lookup. There are three types of cache, Full, Partial and No caching.

Full pre-caching, is when the complete reference data set is read before the input is processed. This is the default caching type.

Partial caching, is when the Lookup transformation specifies the size of the cache that is loaded with reference data. This option is available only with connections that support keyed access.

No caching, the reference data set is accessed by each row in the rowset.

As the default caching type is full, before the start of the data flow, it will cache the lookup data to improve the performance. As the data is inserted after the caching, the lookup it will failed. Therefore, for this kind of implementation you need to set caching type to No Cachin

Action plan: how to get listed in Google's search results for many keywords

2009-03-11T07:18:00.000-07:00

Targeting several keywords on your website is crucial if you want to get high rankings on Google. The more keywords that are related to a special topic can be found on your website, the more likely it is that Google will find your website relevant to that topic.

There are good ways and bad ways to target multiple keywords. If you choose the wrong way, search engines will think that you are a spammer. If you choose the right way, your website will get higher rankings. The following three steps will help you to get high rankings for as many keywords as possible.

Step 1: Remove stuffed keywords from your web pages
Some webmasters think that the best way to optimize a website for multiple keywords is to add them on every web page and separate them with a comma.

These websites usually have extremely long meta tags with dozens if not hundreds of keywords. You won't get high rankings on Google with that method. Add dozens of keywords on a web page is called keyword stuffing and all search engines consider this spam. You actually risk your search engine rankings if you use this method on your web pages.

To do: Check if your web pages contain long keywords lists. If yes, remove them from your pages.

Step 2: Optimize different pages of your website for different keywords
If you add many keyword on the same web page, then the web page will be somewhat relevant to many keywords. If you optimize a web page for one keyword/keyphrase, then your web page will be very relevant to this keyword.

It is much easier to get high rankings if the web page is very relevant to the searched keyword. For that reason, you should optimize as many pages of your website for as many keywords as possible.

The keywords for which you optimize your web pages should be related. If you have a shoe shop, optimize different pages of your website for the keywords sports shoes, sneakers, sandals and boots. By having optimized web pages for these keywords, you show Google that your website is relevant to the topic shoes.

To do: Find the best keywords for your website and then optimize different pages of your website for different keywords.

Step 3: Add keyword modifiers and combine these variations
In step two, you have optimized your web pages for keywords such as sneakers and sandals. The next step is to add modifiers to these keywords on your optimized pages. For example, the sneakers page could include these keyword variations: sneakers shop, nike sneakers, cool sneakers, etc.

It's usually a good idea to combine all of these keyword variations in one sentence (for example for your web page title): Buy cool Nike sneakers in our sneakers shop in London. With this single sentence, your web page can be found for many keyword combinations that are included in this sentence.

To do: Find keyword modifiers and add different keyword variations to your optimized pages.

If you optimize many pages of your website for different but related keywords and if you add variations of each keyword to each page than the content of your web site will be perfectly optimized for high rankings on Google.

If you combine optimized content with good inbound links, your website will get the best possible rankings on Google and other search engines.

Nine factors that affect your website usability

2009-03-11T07:12:00.000-07:00

Optimizing web pages for search engines does not mean creating special pages for search engines. Optimizing web pages for search engines is often the same as optimizing web pages for web surfers.

If you do it correctly, your website will be attractive to both web surfers and search engine spiders. The following list shows nine factors that can improve the usability of your website as well as your search engine rankings.

1. You should have fast loading web pages
Usability: Web surfers don't want to wait for web pages.
Search engine optimization: Search engines can index your web pages more easily.

2. Your web pages should be easy to read
Usability: It's easier for web surfers to read your web pages.
Search engine optimization: Near-white text on a white background and tiny text is considered spam by most search engines.

3. The contents of your web pages should be clearly arranged
Usability: Clear headings, paragraphs and bullet lists make your web pages easier to read.
Search engine optimization: Clear headings, paragraphs and bullet lists make it easier for search engines to find the topic of your web pages.

4. Your web page images should use the IMG ALT attribute
Usability: Web surfers with images turned off and visually impaired visitors will be able to see the content of your images.
Search engine optimization: Search engines cannot index the content of your images but they can index the content of the IMG ALT attribute.

5. You should use custom 404 not found error pages
Usability: If your 404 not found page contains links to other pages of your website or a search form then people might remain on your website.
Search engine optimization: Proper 404 error pages make sure that search engines index the right pages of your website.

6. Your website should be easy to navigate
Usability: Clear and concise navigation links that are easy to find help your website visitors to find content on your site.
Search engine optimization: Clear and concise navigation links that contain your keywords make it easy for search engines to index all of your web pages.

7. Important content is above the fold
Usability: Web surfers with small computer screens can quickly see what your web page is about.
Search engine optimization: The sooner your important content appears in the HTML code of your web pages, the more likely it is that it will be indexed by search engines.

8. Your web page titles are explanatory
Usability: If web surfers bookmark your web pages, a clear web page title will help them to find it again.
Search engine optimization: The web page title is one of the most important SEO elements. It should contain your keywords and it should look attractive so that web surfers click on it when they see your web page title in the search results.

9. The URLs of your web pages are meaningful and self-explanatory
Usability: It's much easier to remember a web page like www.example.com/support than a web page like www.example.com/123123-werwc.php?2342234.
Search engine optimization: If your URLs contain your keywords, this can have a positive effect on your search engine rankings. Dynamic URLs with many variables can lead to problems with search engine spiders.

There are many more factors that influence the position of your website in Google's search results.

What's new in SQL Server 2008 Reporting Services?

2009-03-10T12:07:00.000-07:00

With the release of SQL Server 2008, Reporting Services (SSRS) reached its third version. While the previous version contained many new features, this latest release of SSRS is easier to use and an even more powerful enterprise-level reporting tool. This article will discuss the most significant and most useful new features that make SQL Server 2008 Reporting Services a worthwhile upgrade.

IIS is no longer required
The previous two releases relied on IIS to host the RS Web service and the Report Manager Web application. Many organizations have made a rule of not installing IIS on the same machine as SQL Server for security reasons. This dependency on IIS therefore became a showstopper for using SSRS for many potential users. Based on user feedback, Microsoft re-architected SSRS 2008 and removed this dependency.

SSRS now handles HTTP via HTTP.SYS, a native OS kernel that intercepts and handles HTTP requests just like IIS. If this sounds familiar, that's because it's the same mechanism used by the Native XML Web Services in SQL Server 2005 – a feature that, interestingly, was removed in SQL Server 2008. Microsoft also claims that this implementation provides better performance and scalability. The Reporting Services Configuration tool has been updated to provide management capabilities for Report Manager and Reporting Server services.

Better memory management
Users who have deployed SSRS in high-traffic environments may have noticed performance degradation when many concurrent users access reports with many pages. The reason for the slowness was that SSRS was holding rendered reports in memory, causing problems if memory demands became too high. SSRS now saves rendered pages to the file system to remove the load on server memory. For large reports, it also renders reports as the user is viewing them. This on-demand processing decreases the load on the server by not rendering pages the user might potentially not view at all. In addition to these memory enhancements, you can now also set a threshold on how much memory SQL Server Reporting Services should use.

Export into Microsoft Word
This feature has been requested pretty much since SSRS 2000 came out in 2004, mainly because there are more Word users than Excel users. For whatever reason, this feature did not make it to SQL Server 2005, but it's finally here and certainly a much welcome addition to all the other export options.

Improved charting components
Last year Microsoft acquired Dundas Data Visualization technology for SSRS. Dundas is a company specializing in developing powerful visual components for developers, such as chart, gauge, map and calendar controls. Purchasing the code base for the chart, gauge and calendar controls allowed Microsoft to provide better integration of these components with SSRS and to control the components' future direction. Dundas components have always been visually stunning, so it's no surprise that the new chart component is like night and day compared with chart control in the previous versions of SSRS.

In my opinion, if you do a lot of charting in your reports, this feature alone justifies the effort to upgrade SQL Server 2008 Reporting Services. The new chart region includes many additional chart types, such as cylinder, pyramid, radar and funnel. Other useful enhancements are interval labeling for avoiding label collisions, combining small slices into a single slice in a pie chart and more 2-D and 2-D visual effects.

Tablix data region
This new data region combines the functionality of both table and matrix data regions. This region allows you to combine multiple column groups and row groups, essentially allowing you to create reports that have both dynamic row and dynamic column output. You can now more easily create nested and recursive groups and make them adjacent to each other. There is a lot more to the Tablix data region than I can describe in this space. For more details, read the Understanding the Tablix Data Region section of Microsoft Books Online.

Enhanced CSV export
Comma-separated value (CSV) export can be very useful for automating report processing if you are interested in extracting data elements from the report. SSRS CSV export in previous versions included both the data and the layout in the export. But the inclusion of report layout elements in the CSV output created additional work and complications. The new output format is much cleaner and easier to use for automating programmatic processing.

There are plenty of significant reasons for you to give SSRS 2008 serious consideration. And remember, just like Integration Services, SSRS is a standalone tool you can install on a dedicated server. This means, if your organization is not ready to migrate to SQL Server 2008, you can upgrade just your reporting server and use these new features, using your 2000 or 2005 databases as data sources.

History of SQL Server Integration Services

2009-03-10T12:01:00.000-07:00

Integration Services is the successor to Data Transformation Services (DTS). DTS had humble beginnings. It was started on a shoestring budget with very few resources. Its first incarnation was a proof-of-concept transformation, which later became known as the data pump. The proof of concept caught the attention of some folks around Microsoft, and it was given some funding.

The first release of DTS shipped with SQL Server 7.0 to receptive users. The alternatives at the time were either difficult to work with, expensive, or both. Many DBAs were forced to write custom transformation software, which was inflexible and difficult to maintain. Some tools had limitations, such as the need for source and destination schemas to match exactly, direct dependence on a particular database product, and/or no transformation capabilities. Many wrote custom parsing and transformation applications. For example, many companies are only now converting from hand-coded flat file parsers, SQL scripts, and transformation code to a standard platform such as Integration Services.

The first release of DTS addressed several of these issues and simplified life for a lot of people. By using OLEDB for its data access layer, DTS could access various data sources with little or no custom coding. DTS was also affordable because it shipped "in the box" with SQL Server. Users had access to all the power of more expensive products, yet incurred no additional cost for their ETL tools. This was obviously a benefit to IT shops trying to stretch their budgets. DTS was a flexible product that was easy to use. There were also a number of standard tasks in the box, including the Transform Data, Execute Process, Active X Script, Execute SQL, and Bulk Insert Tasks.

SQL Server 8.0 added even more functionality by adding more tasks. The Execute Package, FTP, and MSMQ Tasks added incremental improvements across the product. However, users experienced some frustration with DTS when attempting to work with large datasets and some of the other limitations inherent in a script-based tool. The time was ripe to create a truly enterprise-ready integration tool.

In 2000, SQL Server decided to make a substantial investment in the ETL and Integration space and brought together some talented folks who formulated the ideas behind the Data Flow Task and the next version of Integration Services. Over a period of five years, the development time frame for SQL Server 2005, the DTS team completely redesigned and rewrote DTS to become Integration Services 2005.

Integration Services 2008 is the next incarnation of that release. While not as revolutionary a release as in 2005, Integration Services 2008 brings some new capabilities and incremental improvements across the product.

What Is SQL Server Integration Services?

2009-03-10T11:58:00.000-07:00

Depending on whom you ask, you might get different answers to that question ranging from descriptions such as a data import/export wizard, to an ETL tool, to a control flow engine, to an application platform, or to a high-performance data transformation pipeline. All are correct because Integration Services is a set of utilities, applications, designers, components, and services all wrapped up into one powerful software application suite. SQL Server Integration Services (SSIS) is many things to many people.

Data Import/Export Wizard
One of the most popular features of Integration Services is the Import/Export Wizard, which makes it easy to move data from a source location such as a flat file or database table to a flat file, table, or other destination. The Import/Export Wizard was the first utility developed back in the SQL Server 7.0 time frame and continues today as an important utility in the database administrator's (DBA) toolbox.

ETL tool
ETL is an acronym for Extract, Transform, and Load and describes the processes that take place in data warehousing environments for extracting data from source transaction systems; transforming, cleaning, deduplicating, and conforming the data; and finally loading it into cubes or other analysis destinations. Although Data Transformation Services (DTS), Integration Services' predecessor application, was considered a valuable tool for doing ETL, Integration Services is where true Enterprise ETL became available in SQL Server.

Control flow engine
The processes involved in moving data from location to location and transforming it along the way are not restricted to only processing data. Integration Services provides a control flow for performing work that is tangentially related to the actual processing that happens in data flow, including downloading and renaming files, dropping and creating tables, rebuilding indexes, performing backups, and any other number of tasks. Integration Services provides a full-featured control flow to support such activities.

Application platform
Developers can create applications that use Integration Services as a platform, embedding the engines within their application using the provided object models. As a developer, you can embed the Integration Services engines and components within your applications using the object models.

High performance data transformation data pipeline
That's a mouthful and really incorporates two ideas: high performance and data pipelining. The Data Flow Task is a high-performance tool because you can use it to perform complex data transformations on very large datasets for incredibly performant processing. The pipeline concept means that you can process data from multiple heterogeneous data sources, through multiple parallel sequential transformations, into multiple heterogeneous data destinations, making it possible to process data found in differing formats and on differing media in one common "sandbox" location.

Performance Tuning SQL Server Backup and Restore

2009-03-10T11:53:00.000-07:00

SQL Server backup and restore performance is often a greater issue than many DBAs realize. This is because the time it takes to backup and restore a database plays a very large part in how large a single SQL Server database can be. For example, if it takes more than 24 hours to backup or restore a database, obviously this presents some problems.
What can sometimes happen to the DBA is that a database may be of reasonable size now, given their current server hardware, and backups and restores may take a reasonable amount of time. But as time goes by, the database grows, and at some point the amount of time to backup and restore the database becomes too long given the backup and restore window available. The moral to this story, keep a close eye on backup and restore times, and factor this in when projecting future hardware needs for your SQL Server databases. [6.5, 7.0, 2000, 2005]

Reasons Why You May Not Want to Use a Heap

2009-03-07T13:10:00.000-08:00

why heaps aren't usually a good choice when designing a database. Now, I didn't say that heaps are always bad, but in most cases, I think they should be avoided (read the list to see why).

As I compiled the list, in some cases I have had the opportunity to verify that they are true, but in other cases, I have not had the time to verify them. I would like your input on the list. Is there anything on the list that is not true, or only partially true (under some circumstances, but not others), and what other reason are there to avoid using heaps that have I left out?

I look forward to your feedback.

If non-clustered indexes are not added to a heap, then all queries against a heap will require table scans to retrieve the requested data. If the heap is large, then these queries will be very resource intensive and hurt SQL Server's overall performance.
Since the data in a heap is unordered, performing a table scan on a heap can cause a lot of extra I/O activity because inefficient random reads, not efficient sequential reads, are more the norm.
While a non-clustered index can be added to a heap to speed up some queries, in almost all cases, to make use of a non-clustered index, the use of a bookmark lookup is required. A bookmark lookup means that once the record(s) to be returned by the query are identified in the non-clustered index, additional reads (the bookmark lookup) must be made of the related rows in the heap, so that all of the data requested by the query is returned. This is not very I/O efficient, especially if many rows are returned. At some point, it may be faster for SQL Server to do a table scan than it is to use a non-clustered index when returning many rows.
Heaps cannot be replicated using SQL Server replication.
If you want to create an XML index on an XML data column, a clustered index must exist on the table.
If you want to create a spatial index on a spatial data column (GEOMETRY or GEOGRAPHY), a clustered index must exist on that table.
If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have to occur. One write for inserting the row, and one write for updating the non-clustered index. On the other hand, if a table has a clustered index as the primary key, inserts take only one write, not two writes. This is because a clustered index, and its data, are one in the same. Because of this, it is faster to insert rows into a table with a clustered index as the primary key than it is to insert the same data into a heap that has a non-clustered index as its primary key. This is true whether or not the primary key is monotonically increasing or not.
When data is updated in a heap, and the updated row is larger than the old row and can't fit into the old space, a forwarding record is inserted into the original location that points to the new location of the page. If this happens a lot, then there is a lot of space wasted in a database maintaining the forwarding records. This also contributes to additional I/O activity as both the pointer, and the row, have to be read.
Even if data updated in a heap is not larger than the old row (the updated data is smaller or the same size than the original data), updating a heap with a non-clustered primary key is slower than updating the same table that has a clustered index as the primary key. This is because updating a table with a clustered index is less write intensive than updating a heap with a non-clustered index as its primary key.
If a row is deleted from a heap with a non-clustered index as its primary key, it is slower than deleting the same row from the same table with a clustered index as its primary key. This is because it takes more I/O to perform this task on a heap than on a clustered index.
When data is deleted from a heap, the data on the page is not compressed (reclaimed). And should all of the rows of a heap page are deleted, often the entire page cannot be reclaimed. This not only wastes space, it contributes to fragmentation of the data pages within a database.
If you take two identical tables, one that is a heap with a non-clustered index as its primary key, and a table that has a clustered index as its primary key, the heap with the non-clustered index will be substantially larger, wasting valuable space and increasing disk I/O.
The ALTER INDEX rebuild and reorganize options cannot be used to defragment and reclaim space in a heap (but they can used to defragment non-clustered indexes on a heap). If you want to defragment a heap, you have three options: 1) create a clustered index on the heap, then drop the clustered index; 2) Use SELECT INTO to copy the old table to a new table; or 3) use BCP or SSIS to move the data from the old table to a new table.

Importing and exporting bulk data with SQL Server's bcp utility

2009-03-05T09:05:00.000-08:00

The Bulk Copy Program (bcp) utility in Microsoft SQL Server enables database administrators to import bulk data into a table or export it from a table into a file. It also supports several options that define how data will be exported, where it will be imported and which data will be loaded.
This tip discusses several examples of bcp commands to bulk-copy data in and out of SQL Server tables. The examples have been tested on SQL Server 2005 and SQL Server 2008, and for all examples I used the AdventureWorks sample database.

Exporting data with the bcp utility

One of the simplest operations that you can perform with the bcp utility is to bulk-copy data out of a SQL Server table into a text file. By entering the command at a Windows command prompt, you can run a bcp command. The following command, for example, copies the data from the Sales.vSalesPerson view in the AdventureWorks database to the C:\Data\SalesPerson.txt file:

bcp AdventureWorks.Sales.vSalesPerson out C:\Data\SalesPerson.txt -c –T

As you can see, the bcp command begins with the utility name, followed by the fully qualified name of the table (database.schema.table). The out keyword is next, which tells the bcp utility that the data will be exported from the table. The path and file name of the target text file follows the out keyword. Note that the command examples in this tip might wrap to multiple lines. All the examples, however, should be run as a single command.

In addition to these basic arguments, the bcp utility supports several switches that control the utility's behavior. In the example above, the -c switch indicates that all data is treated as character data, regardless of how it is stored in the source table. If you do not specify the -c switch or another type-related switch, you will be prompted to specify the type for each column after you enter the bcp command. You may notice that bcp command switches are case sensitive.

The next switch in the above example is -T, which tells the bcp utility to use a trusted connection to connect to the SQL Server instance. If you do not specify -T, you must provide your user name (the -U switch) and your password (the -P switch), or you will be prompted for this information.

Because no instance is specified in the preceding example, the utility uses the default instance on the local machine. To specify a SQL Server instance, use the -S switch, followed by the server name, as in the following example:

bcp AdventureWorks.Sales.vSalesPerson out C:\Data\SalesPerson.txt -c -T -S Server01

The bcp utility will now connect to the default instance on Server01. If you want to connect to an instance other than the default, you must specify the instance name along with the server name, as in Server01\SqlSrv.

By default, the bcp utility uses tabs to separate fields in the target file. You can, however, override that behavior by using the -t switch, as in the following example:

bcp AdventureWorks.Sales.vSalesPerson out C:\Data\SalesPerson.csv -c -T -t,

In this case, a comma is specified after the -t switch, which means that the data fields will now be separated by commas. This allows you to save the data to a .csv file, as I've done here, which can be handy if you want to view the data in a Microsoft Excel file.

These examples have been limited to exporting data from a table. But you can also use the bcp command to run a Transact-SQL query and export the query results. The following bcp command, for example, includes a SELECT statement that retrieves only the SalesPersonID, FirstName, and LastName columns from the vSalesPerson view:

bcp "SELECT SalesPersonID, FirstName, LastName FROM AdventureWorks.Sales.vSalesPerson"
queryout C:\Data\SalesPerson.csv -c -T -t,

In this scenario, the query, enclosed in quotes, is passed into the bcp command instead of the table name. In addition, the out keyword has been replaced with queryout. But the rest of the command is the same as the preceding example. As a result, the SalesPerson.csv file will now contain only the three specified columns. You can also refine your query further: You can include, for example, a WHERE clause that qualifies which rows are returned from the source table.

Importing data with the bcp utility

The bcp utility makes importing data as simple as exporting data. To run the examples in this section, first run the following T-SQL script to create the SalesPeople table in the AdventureWorks database:

USE AdventureWorks
GO
IF OBJECT_ID (N'SalesPeople', N'U') IS NOT NULL
DROP TABLE dbo.SalesPeople
GO
CREATE TABLE dbo.SalesPeople (
SalesPersonID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL
)

To import data, you need a source file from which to copy the data. For instance, the following command uses the recently created SalesPerson.csv file to load data into the SalesPeople table:

bcp AdventureWorks.dbo.SalesPeople in C:\Data\SalesPerson.csv -c -T -t,

First, you must specify the target table, followed by the in keyword that replaces out or queryout in this scenario. Next, you must specify the path and file name of the source file, followed by any applicable switches.

When you run this command and view the results, note that while the source file includes the salesperson IDs, these values are not inserted into the SalesPersonID column. The column is defined as an IDENTITY column, so the source data is ignored. To retain the original values, you must add the -E switch to the command, as in the following example:

bcp AdventureWorks.dbo.SalesPeople in C:\Data\SalesPerson.csv -c -T -t, -E

Your table will now include the desired data.

Using a format file

When importing or exporting data, you may find that the structure of your source data does not match the structure of the target data. The columns in a text file, for example, might not be in the same order as those in your target table, or the number of columns may be different. You can address this issue by creating a format file that maps the structures of the source and destination. Let's look at an example to demonstrate how this works.

Suppose you use the following command to export data from the vSalesPerson view to the SalesPeople.txt file:

bcp "SELECT LastName, FirstName, SalesPersonID FROM AdventureWorks.Sales.vSalesPerson"
queryout C:\Data\SalesPeople.txt -c -T -t,

This command uses the same arguments as those in previous examples. Note, however, the order in which the columns are retrieved from the view: LastName, then FirstName, and finally SalesPersonID.

Now suppose you plan to use this file to import data into the SalesPeople table. The columns in the table, however, are defined in a different order from that in the text file. To address this issue, you can create a format file to map the columns from the source to the destination. The following command demonstrates how to create a format file:

bcp AdventureWorks.dbo.SalesPeople format nul -f C:\Data\SalesPeople.fmt -c -T -t,

As previous examples show, the command first specifies the target table. This time, however, the table name is followed by the keywords format nul, which indicate that the bcp utility should create a format file. The -f argument is then used to specify a path and file name for the format file, followed by several switches. As a result, when you run this command, a format file is generated that contains the structure of the SalesPeople table.

The following data shows the content of the SalesPeople.fmt format file that is generated by the above command:

10.0
3
1 SQLCHAR 0 12 "," 1 SalesPersonID ""
2 SQLCHAR 0 100 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "\r\n" 3 LastName SQL_Latin1_General_CP1_CI_AS

The first row of the file (10.0) identifies the version of bcp that is being used. The second row (3) identifies the number of columns in the table, and the next three rows provide information about the columns themselves:

The first field indicates the order in which the columns should appear in the source document.

The second field shows the source file data type for each column. Because I specified the -c switch when I generated the file, a character data type is used for all fields as they are extracted from the data file. SQL Server will then convert this data into the correct types as the data is inserted.

The third field shows the prefix length for the field, which is used by SQL Server to provide the most compact file storage. A value of 0 is used automatically if you specify the -c switch when creating the format file.

The fourth field indicates the number of bytes for the data type of a particular field.

The fifth field shows how columns and rows are terminated. Because the -t switch was used when creating the format file, the field values of the source file must be terminated by commas.

The sixth field maps to the order in which the columns appear in the SQL Server table.

The seventh and final field provides the collation information for character columns in the SQL server table.

To use the format file to import data into the SalesPeople table, we must modify the file as follows:

10.0
3
1 SQLCHAR 0 100 "," 3 LastName SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 1 SalesPersonID ""

As you can see, the order of the columns has been modified to reflect how they appear in the format file. The SalesPersonID column is now listed last and it is terminated with \r\n. The LastName column is now listed first and is terminated with a comma.

After you modify and save the format file, it is ready to use in your bcp commands. The following example demonstrates how to call the format file:

bcp AdventureWorks.dbo.SalesPeople in C:\Data\SalesPeople.txt -f C:\Data\SalesPeople.fmt –T

Note that, when you import data from the SalesPeople.txt file, you must also use the -f switch to call the format file. Also notice that you no longer need to include the -t and -c switches because the information is now included in the format file.

Passing a Table to a Stored Procedure

2009-02-13T02:19:00.000-08:00

SQL Server 2005 and previous versions do not support passing a table variable to a stored procedure.

This article introduces the new feature added to SQL Server 2008, which supports passing a TABLE to a stored procedure or function.

This article is based on SQL Server 2008 CTP 3. Some of the information may change by the time the product is finally released.

Before we create a Function or Stored Procedure that accepts a TABLE variable, we need to define a User Defined TABLE Type. SQL Server 2008 introduced a new User defined TABLE type. A TABLE type represents the structure of a table that can be passed to a stored procedure or function.

So the first step is to create a User Defined TABLE type. The following TSQL code creates a User defined TABLE type named "ItemInfo".

CREATE TYPE ItemInfo AS TABLE (
ItemNumber VARCHAR(50),
Qty INT )

You can use the system view SYS.TYPES to see the type that you have just created. The following query returns all the types defined in the system.

SELECT * FROM SYS.TYPES
/* If you just need to find information about the TABLE types, you could find it from the following TSQL query.*/
SELECT * FROM SYS.TYPES WHERE is_table_type = 1
/* There is another view, which is handy to find information about TABLE types. */
SELECT * FROM SYS.TABLE_TYPES

We have created a TABLE type that we need. Now let us see how it works. Let us create a variable of type "ItemInfo" and try to insert a few records to it. Then lets query the table variable to see if the information is correctly inserted. [code]

/* Let us declare a variable of type ItemInfo which is a TABLE Type */

DECLARE @items AS ItemInfo
/* Insert values to the variable */
INSERT INTO @Items (ItemNumber, Qty)

SELECT '11000', 100 UNION ALL

SELECT '22000', 200 UNION ALL
SELECT '33000', 300

/* Lets check if the values are correctly inserted or not */

SELECT * FROM @Items

/* OUTPUT:
ItemNumber Qty
-------------------------------------------------- -----------
11000 100
22000 200
33000 300
*/

Now let us create a stored procedure that accepts a TABLE variable. Let us create a very simple stored procedure which accepts a TABLE variable and SELECTs contents of the table.

1 CREATE PROCEDURE TableParamDemo
2 (
3 @Items ItemInfo
4 )
5
6 AS
7
8 SELECT *
9 FROM @Items

Well, this would generate the following error:

1 /*
2 Msg 352, Level 15, State 1, Procedure TableParamDemo, Line 1
3 The table-valued parameter "@Items" must be declared with the READONLY option.
4 */

A table variable that is passed to a stored procedure or function should be marked as READONLY. The "callee" cannot modify the table being passed into it. Here is the correct code.

1 CREATE PROCEDURE TableParamDemo
2 (
3 @Items ItemInfo READONLY
4 )
5
6 AS
7
8 SELECT *
9 FROM @Items

Now let us execute the stored procedure we just created. Run the following code.

1 /*
2 declare the variable
3 */
4 DECLARE @items AS ItemInfo
5
6 /*
7 Insert values to the variable
8 */
9
10 INSERT INTO @Items (ItemNumber, Qty)
11 SELECT '11000', 100 UNION ALL
12 SELECT '22000', 200 UNION ALL
13 SELECT '33000', 300
14
15 /*
16 Execute the procedure
17 */
18 EXECUTE TableParamDemo @Items
19
20 /*

21 OUTPUT:
22
23 ItemNumber Qty
24 -------------------------------------------------- -----------
25 11000 100
26 22000 200
27 33000 300
28
29 */

You cannot modify the TABLE parameter passed into the stored procedure. If you try to do so, you will get an error as shown in the following example.

1 CREATE PROCEDURE TableParamDemo
2 (
3 @Items ItemInfo READONLY
4 )
5
6 AS
7
8 SELECT *
9 FROM @Items
10
11 INSERT INTO @Items (ItemNumber, Qty)
12 SELECT '1001', 20
13
14 /*
15 OUTPUT:
16
17 Msg 10700, Level 16, State 1, Procedure TableParamDemo, Line 11
18 The table-valued parameter "@Items" is READONLY and cannot be modified.
19 */
Conclusions

The support for TABLE variables is very interesting. While working with User Defined TABLE Type, please note that you cannot use it as a column of a table. Please also note that, once created, you cannot alter the structure of the TABLE.

SQL Server Federated Database Performance Tuning

2009-02-13T02:16:00.000-08:00

For very large databases, consider using federated database servers to balance the processing load across multiple SQL 2000 or 2005 servers. This technique horizontally partitions SQL 2000 or 2005 data over one or more SQL Servers, allowing the client application to send SQL statements to the server in the federation having most of the data required by the statement. This way, the query load is spread over multiple SQL 2000 or 2005 servers.

Federated database servers works best for databases that can be naturally partitioned over multiple servers. For example, if the data can be segregated by product line or geographical location, then the data can easily be separated over multiple databases in a federation.

Here's a federated database might work. Let's say that a company has customers in North America, South America, Europe, and Asia, and that the data stored for every customer, no matter where they are located, is identical. One option would be to partition the data based horizontally on continent. In this case, four different SQL Servers would be needed in the federation, each storing its respective data.

In almost all cases, when a query is run against the customer data stored in the federated SQL Servers, the data will all be related to a specific continent. Because of this, only one SQL Server will be hit with the query, not all of the SQL Servers. And assuming that customers in all continents will be queried, the separation of customers by continent on different SQL Servers will spread the queries among all of the servers, allowing greater performance and scalability. [2000, 2005]

String comparison: binary vs. dictionary

2009-02-08T07:15:00.000-08:00

It is well known that you get better performance if you compare two strings in a binary collation than in a dictionary collation. But is the performance difference significant enough to warrant the use of an explicit COLLATE clause in the string comparison expression? That was a question came up in a conversation I had with a colleague recently.

To get a feel for the extent of the performance difference, I ran several tests comparing two commonly-used collations:

· Latin1_General_BIN, and
· SQL_Latin1_General_CP1_CI_AS

More specifically, I ran the following T-SQL script in a loop for 100 times:

DECLARE @s1 varchar(max), @s2 varchar(max), @i int
DECLARE @dummy int, @dt datetime
DECLARE @length int

SET @length = 1000000 -- or 100, or 1000000

SELECT @s1 = REPLICATE(CAST('a' as varchar(max)), @length)
SELECT @s2 = REPLICATE(CAST('a' as varchar(max)), @length)

SELECT @i = 1, @dt = GETDATE()

WHILE @i < 1000
BEGIN
IF @s1 = @s2 COLLATE SQL_Latin1_General_CP1_CI_AS
--IF @s1 = @s2 COLLATE Latin1_General_BIN
SET @dummy =0
SET @i = @i + 1
END

SELECT DATEDIFF(ms, @dt, GETDATE())

The script was run in a number of scenarios:

· The @length variable was set to 100, 10,000, and 1,000,000 to see the impact of the string length on the comparison method,

· The string comparison was done in the IF clause with either the Latin1_General_BIN collation or the SQL_Latin1_General_CP1_CI_AS collation

The most salient point to note is that as the string length increases, the performance difference between the two comparison methods increases. While there was no performance difference when the strings were 100 bytes long, the binary comparison was about twice as fast as the dictionary comparison when the string length was 10,000 bytes. When the string length increased to 1,000,000, the binary comparison was about seven times faster.

So, if you are comparing two short strings, you probably shouldn’t bother to explicitly cast the comparison into a binary collation. But when the strings can be very long, it can result in a huge performance improvement to explicitly specify a binary collation for string comparison.

Note that string comparison in COLLATE Latin1_General_BIN and string comparison in COLLATE SQL_Latin1_General_CP1_CI_AS have different semantics. But there are many cases where you have control over your data, and the difference in comparison semantics does not matter (e.g. when you know your data is all in lower case).

Nothing is particularly new here. Just want to contribute some data points to the community.

Indexed View Performance Tuning and Optimization

2009-02-01T22:53:00.000-08:00

If your application needs to access views often, consider adding a unique clustered index to your views to significantly improve performance. When a view is created with a unique clustered index, the view is created with the clustered index, which is created and stored in the database the same way as a clustered index on a table.

Once a unique clustered index has been created for a view, you can also create non-clustered indexes for the same view, which can be used by queries against the view to enhance performance.

As the underlying tables of the view are modified, the clustered index, and any non-clustered indexes of the view, is modified so that it is always up-to-date when it is accessed. And just like indexes on tables, indexes on views experience modification overhead. So only add an index to a view if the benefit of its speed increase when running exceeds the time it takes to update the view's index.

Indexed views can be used by SQL Server two different ways. First, the view can be called directly from a query, as conventional views are currently used. But instead of running the view's underlying SELECT statement and creating the view's result set on the fly, it uses the unique clustered index to display the results of the view almost immediately. Second, any query that is run on SQL Server 2000/2005 is automatically evaluated to see if any existing indexed views exist that would fulfill the query. If so, the Query Optimizer uses the indexed query, even though it has not been specified in the query, greatly speeding the query.

To get the most benefit out of indexed views, you need to use the SQL Server 2000/2005 Enterprise Edition. While you can create indexed views in the other editions of SQL Server 2000/2005, they will not be automatically considered by the query optimizer, and they require the use of the NOEXPAND hint to be used. [2000, 2005]

Good bye

2009-01-30T06:23:00.000-08:00

Not sure any words or condolences make an announcement like this any less painful. Families and friends are directly impacted in a negative way when you hear about news like this and I consider all of you as friends and extended family members. I don't even want to think about the anger and disappointment everyone feels over there. To say I am shocked and confused is an understatement.

Playing a blame game of what went wrong and where did it go wrong leaves me with no answers or directions. Its easy to blame it on the current Economy of the USA, and perhaps I have been somewhat blinded by the fact it has not already hit our company. I assumed this was hitting companies much bigger than ourselves that may have been overstaffed to start with. Thinking it was more manufacturing based than educational based. Did not correlate the massive amounts of home forclosures tied to taxes which support the educational world. (90% comes from state taxes). Did not think about the companies and how they pay taxes on profits. Once again, companies all downsizing as there are no profits, hence no taxes paid by companies either. Here is a local story I found online today as well. http://www.waow.com/Global/Story.asp?S=9442775 I heard more about things like this on the news last night. (Keep in mind Wisconsin is only 1 state among many we are infiltrated with). Seems like every night I turn on the news, there is a new local company cutting jobs. Listen to the radio and massive layoffs for other companies forcasting billion dollar losses. Shutting down stores, factories.

I hope our time together would be classified as one of the best experiences in our lives. I hope we have allowed you to become better programmers than when you first started based on the programs you have developed. I hope Suresh and myself will be remembered as one of the best managers you have ever had the ability to work for. Perhaps this is not true with the most recent of events, but I am SO PROUD of the work you have done. As a team, you guys have shown so much growth thru our time together.

Anil, you have been a rock on this team with your programming practices and delivery builds. I think your fundamentals have improved greatly as well as your ability to take complex concepts and ideas (Program Selection on CMT, SystemInfo.dll, Object Oriented Class level project builds) and bring them to life. There is no doubt in my mind that you will go on to bigger and better things in the programming world. You have what it takes to be a great programmer in this world.

Its hard to talk about success when external factors force our working relationships to be closed and terminated. I will never consider the time we spent working together anything less than professional, productive and honorable. I was lucky to be able to work with a staff as great as what you have been. I really do hope you stay in touch and keep me up to date on personal as well as professional experiences in your lives. This is a sad day for Renaissance Learning and one I never want to be part of again. Several at RLI home offices have also been let go and over the next few months, more could happen. Its a scary time that nobody wants any part of. I hear from my grandparents about the Great Depression and how bad it was. Its a different time now, yet watching so many people lose their jobs, even outside of RLI and surrounding communities makes me fear that it will be a long and difficult journey back to where we need to be. I just hope we remember what it took to get us there and all the people that were sacrificed in the process.

Anil, if you ever need a reference, don't be afraid to email me. Currently I check my work email more often than my personal one, but as we have seen, things can change way to quick. So stay in touch to both accounts.

Thank you for everything you have developed for us as its been more than I could have asked for.

Restore a SQL Server 2008 Database into SQL Server 2005

2009-01-27T21:36:00.000-08:00

If you've tried to back up a database in SQL Server 2008 and then restore it into SQL Server 2005, you know that the database backups are not backward compatible. However, with SQL Server 2008 Management Studio, you can script data and schemas in SQL Server 2005 mode. This 10-Minute Solution takes you through the steps to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005.

Problem
Transferring databases from SQL Server 2008 to SQL Server 2005 is not possible with standard backup and restore facilities.

Solution
Leverage the scripting wizard in SQL Server 2008 to script data as well as schemas in SQL Server 2005 compatibility mode.

Using the "Generate SQL Server Scripts" Wizard
The Northwind database is no longer shipped as part of the SQL Server installation, but you can download it from go.microsoft.com. The data is scripted as INSERT statements.

To create the scripts, you have to run the "Generate SQL Server Scripts" wizard, which you can run within SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on the database and selecting "Tasks –> Generate Scripts."

1. Click "Script all objects in the selected database" (see Figure 2), and then click "Next."

2. Amend the script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True" (see Figure 3). (SQL Server 2000 is also supported.) If you are putting the database on a new instance for the first time, make sure the "Script Database Create" option is set to "True." Click "Next" when you are happy with the options.

3. Select "Script to file," select the file name, and choose "Single file" and Click "Next" for a summary
4. Now click on "Finish" to get progress messages while the script runs and completes.
If the generation process fails, then you can use the "Report" option to see why.

5. When the scripting is completed, look for the following lines:

CREATE DATABASE [Northwind] ON PRIMARY

(NAME = N'Northwind', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.mdf' ,
SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

(NAME = N'Northwind_log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:

--EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'
--GO

Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment. You can now test data against SQL Server 2008 and SQL Server 2005.

The Inevitable Limitations
Of course, this technique is not without its limitations. Here are a few to bear in mind:

* The data is insecure, as it is in clear readable text. So if you are using real data, you should delete the file created once you have loaded it into SQL Server 2005. You can regenerate the file from the SQL 2008 backup, if necessary.
* If you have a database with a large amount of data, the script file, of course, will be huge.
* SQL Server 2008 specifics in the source database will not be migrated.

SQL Server 2008 Management Studio Trick

2009-01-27T21:26:00.000-08:00

I had a database selected in the Object Explorer window and I had the Object Explorer Details window open. I noticed a little icon at the bottom of the screen:

Then I saw that the bar above it was a moveable bar. So I moved it and saw this:

Whoa! So then I tried a table, HumanResources.Department from AdventureWorks2008:

Which caused me to check a procedure:

Each line has a little icon on the side that lets you copy it, line by line. It’s really just a way to display the basic properties about the object in a readily accessible format. It’s not going to change my life, or yours, but it sure is handy to know a quick way to access some basic information.

F4 is also a lot more handy in SSMS these days than it used to be. My favorite is when you hit F4 after clicking on a node in a graphical execution plan. But it works for several other things as well, including tables, procedures, etc. And doesn’t make you keep object explorer details open (which causes some problems of its own, at least pre-CU3).

SQL Server Query Execution Plan Analysis

2009-01-27T21:19:00.001-08:00

If your SQL Server has multiple CPUs, and you have not changed the default setting in SQL Server to limit SQL Server's ability to use all of the CPUs in the server, then the query optimizer will consider using parallelism to execute some queries. Parallelism refers to the ability to execute a query on more than one CPU at the same time. In many cases, a query that runs on multiple processors is faster than a query that only runs on a single processor, but not always.

The Query Optimizer will not always use parallelism, even though it potentially can. This is because the Query Optimizer takes a variety of different things into consideration before it decides to use parallelism. For example, how many active concurrent connections are there, how busy is the CPU, is there enough available memory to run parallel queries, how many rows are being processed, and what is the type of query being run? Once the Query Optimizer collects all the facts, then it decides if parallelism is best for this particular run of the query. You may find that one time a query runs without parallelism, but later, the same query runs again, but this time, parallelism is used.

In some cases, the overhead of using multiple processors is greater than the resource savings of using them. While the query processor does try to weigh the pros and cons of using a parallel query, it doesn't always guess correctly.

If you suspect that parallelism might be hurting the performance of a particular query, you can turn off parallelism for this particular query by using the OPTION (MAXDOP 1) hint.

The only way to know for sure is to test the query both ways, and see what happens. [7.0, 2000, 2005]

How can I confirm whether a SQL Server installation is 32 or x64 bit?

2009-01-21T20:34:00.000-08:00

How about to confirm the component type when a particular SQL Server 2005 instance has been installed?

You can obtain the relevant information using a TSQL statement or referring to the SQL installation directory on that Windows server. Each of these options are outlined below:

Transact-SQL statement:

Execute select @@version from Query Editor or Query Analyzer and the results will be :

SQL 90

Microsoft SQL Server 2005 - 9.00.2153.00 (X64) May 9 2006 13:58:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft CorporationStandard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

SQL 80

Microsoft SQL Server 2000 - 8.00.2148 (Intel X86) Jul 7 2005 20:33:10 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

If the installation is 32 bit then as highlighted above you will see 'Intel X86', if not then you will see 'X64' after the SQL Server version number.

SQL Server Installation Directory:

The default installation directory for SQL Server is \Program Files\ and the same can be referenced as below on a x64 operating system:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

32-bit:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

For more information on the SQL Server 2005 x64 bit refer to
http://www.microsoft.com/sqlserver/2005/en/us/64-bit.aspx

How to import an Excel file which has columns with more than 255 characters to SQL Server using SSIS?

2009-01-21T20:14:00.000-08:00

When the driver determines that an Excel column contains text data, the driver selects the data type based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row.

You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. By default it is 8 rows.

Uninstall and Remove Multiple Database Instances of Microsoft SQL Server 2005

2009-01-21T03:17:00.000-08:00

When database system administrator installs Microsoft SQL Server 2005 and creates a few database engine instances, there will be problem and hard time to remove and uninstall these multiple database instance components and the corresponding SQL Server services. When users try to uninstall Microsoft SQL Server 2005 or remove database instance components using Add and Remove Programs or Uninstall or change a programs (Vista) in Control Panel, only one SQL Server 2005 database engine can be selected. After uninstallation has completed, when users attempt to run the SQL Server 2K5 Uninstall to completely clean remove any other instances or components, the action will fail with error message saying the uninstaller shortcut entry is invalid and asked if you want to remove the link.

When you run Add and Remove Programs or Programs and Features (for Windows Vista) in Control Panel, it actually executes the ARPWrapper.exe program that is installed by Microsoft SQL Server Setup Support Files component by using the /Remove option. However, when ARPWrapper.exe completes its removal task, the program also uninstall Microsoft SQL Server Setup Support Files component and hence removes itself or deletes the reference to the ARPWrapper.exe program. This is the cause that prevents administrators to run the uninstaller again to remove database instances or uninstall SQL Server 2005.

The resolution to remove multiple database engine instances from Microsoft SQL Server 2005 is to install ARPWrapper.exe again after removing each components. The following guide provides step-by-step instructions on how to uninstall stand-alone or multiple instance of SQL Server 2005 manually.

1. Login to the server with an administrator user account, or SQL Server 2005 service account.
2. Stop all active SQL Server services in Services applet in Control Panel as any active connections may prevent the uninstallation process from finishing successfully.
3. Go to Control Panel and then Add or Remove Programs applet.
4. Search and verify that Microsoft SQL Server Setup Support Files appears in the list of installed programs. If it’s found, proceed to next step. If Microsoft SQL Server Setup Support Files component is not listed as one of the installed program, install the component by running sqlsupport.msi file locates in Servers\setup\ folder of the original SQL Server 2005 installation media, or disc, folder and USB flash drive that contains SQL Server 2005 setup files.
5. Open command prompt (in Vista, use command prompt with administrator rights), and run the following command to uninstall the SQL Server components:

%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove
6. Uninstall the SQL Server instance components such as database engine one at a time.

Uninstall SQL Server 2005 Components

7. Once the removal completes, repeat all of the above steps again until all the SQL Server components are uninstalled.
8. Finally, uninstall the Microsoft SQL Server Setup Support Files component by using Add or Remove Programs (It should has been removed, but double check to ensure that).

Above guide assumes that you didn’t encounter any other problems, and simply want to get rid of all DB instances of the SQL Server 2005. If you have other issues, refer to KB909906 on how to manually uninstall SQL Server.

25 most dangerous programming errors

2009-01-20T21:32:00.000-08:00

The SANS Institute has explanations of the 25 most dangerous programming errors, according to security experts from all over the world working for a number of different computer security organizations. As pointed out early in the article:

The impact of these errors is far reaching. Just two of them led to more than 1.5 million web site security breaches during 2008 - and those breaches cascaded onto the computers of people who visited those web sites, turning their computers into zombies.

The 25 errors, organized by type, are:

Insecure Interaction Between Components

* Improper Input Validation
* Improper Encoding or Escaping of Output
* Failure to Preserve SQL Query Structure
* Failure to Preserve Web Page Structure
* Failure to Preserve OS Command Structure
* Cleartext Transmission of Sensitive Information
* Cross-Site Request Forgery
* Race Condition
* Error Message Information Leak

Risky Resource Management

* Failure to Constrain Operations within the Bounds of a Memory Buffer
* External Control of Critical State Data
* External Control of File Name or Path
* Untrusted Search Path
* Failure to Control Generation of Code
* Download of Code Without Integrity Check
* Improper Resource Shutdown or Release
* Improper Initialization
* Incorrect Calculation

Porous Defenses

* Improper Access Control
* Use of a Broken or Risky Cryptographic Algorithm
* Hard-Coded Password
* Insecure Permission Assignment for Critical Resource
* Use of Insufficiently Random Values
* Execution with Unnecessary Privileges
* Client-Side Enforcement of Server-Side Security

Seek or scan?

2009-01-18T20:37:00.000-08:00

One very common question that I see on the forums is on index seeks and index scans. A query is resulting in a table/clustered index scan, even though there’s an index on one or more of the columns been searched on.

One of the more common reasons that this happens is because the index in question is not covering, and SQL has determined that the cost of doing the lookups to fetch the extra columns is higher than the cost of scanning the entire table.

If an index does not cover a query, then bookmark lookups are required to get the additional columns, bookmark lookups are run one row at a time, and are seeks on the clustered index. Hence it’s clear that bookmark lookups on a large number of rows are exceedingly expensive and that is why SQL will switch to a clustered index/table scan when lookups are required on a significant percentage of the rows in the table.

So, what consitutes a significant percentage of the rows in the table? 50%? 20%? 10%?

The answer’s often surprising. It’s under 1% of the total rows in the table.

Here’s some test code to demonstrate that.

Setup code:

# CREATE TABLE [dbo].[SeekOrScan](
# [ID] [int] IDENTITY(1,1) NOT NULL primary key,
# SomeNumber int,
# padding char(100)
# ) ON [PRIMARY]
#
# CREATE NonCLUSTERED INDEX [idx_SeekOrScan] ON [dbo].[SeekOrScan] (SomeNumber)

Test code:

1. insert into [SeekOrScan] (SomeNumber)
2. select top 1000000 0
3. from master..spt_values a cross join master..spt_values b
4. where a.name is null and b.name is null
5.
6. update [SeekOrScan] set SomeNumber = ID -- just so we've got sequential numbers for the between
7. GO
8.
9. dbcc freeproccache
10.
11. select * from [SeekOrScan]
12. where somenumber between 1 and 100000 -- 10% of table
13. -- Clustered index scan
14.
15. dbcc freeproccache
16.
17. select * from [SeekOrScan]
18. where somenumber between 1 and 50000 -- 5% of table
19. -- Clustered index scan
20.
21. dbcc freeproccache
22.
23. select * from [SeekOrScan]
24. where somenumber between 1 and 10000 -- 1%
25. -- Clustered index scan
26.
27. dbcc freeproccache
28.
29. select * from [SeekOrScan]
30. where somenumber between 1 and 5000 -- 0.5% of table
31. -- clustered index scan
32.
33. dbcc freeproccache
34.
35. select * from [SeekOrScan]
36. where somenumber between 1 and 3000 -- 0.3% of table
37. -- nonclustered index seek

insert into [SeekOrScan] (SomeNumber) select top 1000000 0 from master..spt_values a cross join master..spt_values b where a.name is null and b.name is null update [SeekOrScan] set SomeNumber = ID -- just so we've got sequential numbers for the between GO dbcc freeproccache select * from [SeekOrScan] where somenumber between 1 and 100000 -- 10% of table -- Clustered index scan dbcc freeproccache select * from [SeekOrScan] where somenumber between 1 and 50000 -- 5% of table -- Clustered index scan dbcc freeproccache select * from [SeekOrScan] where somenumber between 1 and 10000 -- 1% -- Clustered index scan dbcc freeproccache select * from [SeekOrScan] where somenumber between 1 and 5000 -- 0.5% of table -- clustered index scan dbcc freeproccache select * from [SeekOrScan] where somenumber between 1 and 3000 -- 0.3% of table -- nonclustered index seek

So somewhere around 0.4% of the table, the index seek with bookmark lookup became more expensive than a table scan.

Optimizing SQLServer 2008

2009-01-14T22:17:00.000-08:00

Optimization in SQL Server 2008
Using parameterized queries is a well known SQL Server Best Practice. This technique ensures caching and reuse of existing query execution plans (instead of constantly compiling new plans), as well as avoiding SQL injection by mandating that input data be data type safe.
See more about SQL Server parameterization Best Practices here: http://blogs.msdn.com/sqlprogrammability/archive/2007/01/13/6-0-best-programming-practices.aspx
An application that I work with presented me with an interesting dilemma; It wanted to utilize the benefits of plan reuse but the parameter values that the application initially sends to SQL Server are not representative of the values passed in the subsequent re-execution of the statement. SQL Server compiled and cached a ‘good’ plan for the first parameter values. Unfortunately, this had the unintended side effect of caching a poor execution plan for all subsequent parameter values. To make this clearer let’s look at the following example query;
Select * from t where col1 > @P1 or col2 > @P2 order by col1;
Let’s assume for simplicities sake that col1 is unique and is ever increasing in value, col2 has 1000 distinct values and there are 10,000,000 rows in the table, and that the clustered index consists of col1, and a nonclustered index exists on col2.
Imagine the query execution plan created for the following initially passed parameters: @P1= 1 @P2=99
These values would result in an optimal queryplan for the following statement using the substituted parameters:
Select * from t where col1 > 1 or col2 > 99 order by col1;
Now, imagine the query execution plan if the initial parameter values were: @P1 = 6,000,000 and @P2 = 550.
As before, an optimal queryplan would be created after substituting the passed parameters:
Select * from t where col1 > 6000000 or col2 > 550 order by col1;
These two identical parameterized SQL Statements would potentially create and cache very different execution plans due to the difference of the initially passed parameter values. However, since SQL Server only caches one execution plan per query, chances are very high that in the first case the query execution plan will utilize a clustered index scan because of the ‘col1 > 1’ parameter substitution. Whereas, in the second case a query execution plan using index seek would most likely be created.
Unfortunately if the initial parameter values are similar to the first example above, then a ‘table scan’ execution plan gets created and cached, even though most of the following queries would rather use a plan that contains the index seek.
There are a number of ways to work-around this issue;
· Recompile every time the query is executed using the RECOMPILE hint - This can be very CPU intensive and effectively eliminates the benefits of caching queryplans.
· Unparameterize the query – Not a viable option in most cases due to SQL injection risk.
· Hint with specific parameters using the OPTIMIZE FOR hint (However, what value(s) should the app developer use?) This is a great option if the values in the rows are static, that is; not growing in number, etc. – However in my case the rows were not static.
· Forcing the use of a specific index
· Use a plan guide – Using any of the recommendations above.
SQL Server 2008 provides another alternative: OPTIMIZE FOR UNKNOWN

SQL Server 2008 provides a different alternative; the OPTIMIZE FOR UNKNOWN optimizer hint. This hint directs the query optimizer to use the standard algorithms it has always used if no parameters values had been passed to the query at all. In this case the optimizer will look at all available statistical data to reach a determination of what the values of the local variables used to generate the queryplan should be, instead of looking at the specific parameter values that were passed to the query by the application.
Full documentation of optimizer hints can be found here:
http://msdn.microsoft.com/en-us/library/ms181714(SQL.100).aspx
Example:
@p1=1, @p2=9998,
Select * from t where col > @p1 or col2 > @p2 order by col1
option (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN))
Using this new optimizer hint option has allowed the ISV to generate queries that result in the benefits of parameterization; such as plan reuse, while eliminating the problems caused by the caching of queryplans that were created using nontypical initially passed parameter values.
NOTE: This new optimizer hint option, like all optimizer hints, should be used only by experienced developers and database administrators in cases where SQL Server cannot create an optimal plan.
Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 4 Comments
Friday, October 31, 2008 1:49 PM
SQL Server 2008 : new binary – hex string conversion functionality can dramatically improve related query performance by orders of magnitude.
In previous SQL Server releases it wasn’t possible to convert binary data to string characters in hex format directly, because SQL Server did not have a built-in Transact-SQL command for converting binary data to a hexadecimal string. The Transact-SQL CONVERT command converted binary data to character data in a one byte to one character fashion. SQL Server would take each byte of the source binary data, convert it to an integer value, and then uses that integer value as the ASCII value for the destination character data. This behavior applied to the binary, varbinary, and timestamp datatypes.

The only workarounds were to use either a stored procedure as described in a Knowledge Base Article: "INFO: Converting Binary Data to Hexadecimal String" ( http://support.microsoft.com/kb/104829 ) or by writing a CLR function.

An ISV I work with doesn’t support CLR and therefore they implemented their own version of a custom convert function in form of a stored procedure. This one was even faster than everything else they found on the Internet.

NEW – IN SQL SERVER 2008 the convert function was extended to support binary data – hex string conversion. It looks like a tiny improvement almost not worth mentioning.

However, for the ISV it was a big step forward as some critical queries need this functionality. Besides the fact that they no longer have to ship and maintain their own stored procedure, a simple repro showed a tremendous performance improvement.

Repro:
=====

I transformed the procedure described in the KB article mentioned above into a simple function. The stored procedure below will create a simple test table with one varbinary column and insert some test rows in 10K packages ( e.g. nr_rows = 100 -> 1 million rows in the table ).

The repro shows two different test cases:
1. insert 0x0 two million times
2. insert 0x0123456789A12345 two million times

Depending on the length of the value the disadvantage of the stored procedure solution will be even bigger. On my test machine the results of the test queries below were:
(both tests were done with the same SQL Server 2008 instance - no change of any settings)

1. two million times value 0x0

a, using stored procedure : about 3460 logical reads, no disk IO, ~52 secs elapsed time
b, using new convert feature : about 5200 logical reads, no disk IO, < 1 sec elapsed time

2. two million times value 0x0123456789A12345
a, using stored procedure : about 3460 logical reads, no disk IO, ~157 secs elapsed time
b, using new convert feature : about 5200 logical reads, no disk IO, < 1 sec elapsed time

Repro Script:
========

create function sp_hexadecimal ( @binvalue varbinary(255) )
returns varchar(255)
as
begin
declare @charvalue varchar(255)
declare @i int
declare @length int
declare @hexstring char(16)
select @charvalue = '0x'
select @i = 1
select @length = datalength(@binvalue)
select @hexstring = '0123456789abcdef'
while (@i <= @length)
begin
declare @tempint int
declare @firstint int
declare @secondint int
select @tempint = convert(int, substring(@binvalue,@i,1))
select @firstint = floor(@tempint/16)
select @secondint = @tempint - (@firstint*16)
select @charvalue = @charvalue +
substring(@hexstring, @firstint+1, 1) +
substring(@hexstring, @secondint+1, 1)
select @i = @i + 1
end
return ( @charvalue )
end

create procedure cr_conv_test_table ( @value varbinary(16), @nr_rows int )
as
begin
declare @exist int
declare @counter int
set NOCOUNT ON
set statistics time off
set statistics io off
set statistics profile off
set @exist = ( select count(*) from sys.objects
where name = 'conv_test_table' and
type = 'U' )
if( @exist = 1 )
drop table conv_test_table

set @exist = ( select count(*) from sys.objects
where name = 'conv_test_table_temp' and
type = 'U' )
if( @exist = 1 )
drop table conv_test_table_temp

create table conv_test_table ( varbincol varbinary(16) )
create table conv_test_table_temp ( varbincol varbinary(16) )
set @counter = 10000
while @counter > 0
begin
insert into conv_test_table_temp values ( @value )
set @counter = @counter - 1
end
set @counter = @nr_rows
while @counter > 0
begin
insert into conv_test_table select * from conv_test_table_temp
set @counter = @counter - 1
end
end

-- create 2 million test rows
execute cr_conv_test_table 0x0, 200

set statistics time on
set statistics io on

-- compare runtime of stored procedure with new convert feature
select count(*) from conv_test_table
where dbo.sp_hexadecimal(varbincol) = '0x00'
select count(*) from conv_test_table
where CONVERT(varchar(255),varbincol,1) = '0x00'

-- create 2 million test rows
execute cr_conv_test_table 0x0123456789A12345, 200

set statistics time on
set statistics io on

-- compare runtime of stored procedure with new convert feature
select count(*) from conv_test_table
where dbo.sp_hexadecimal(varbincol) = '0x0123456789A12345'
select count(*) from conv_test_table
where CONVERT(varchar(255),varbincol,1) = '0x0123456789A12345'

Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 0 Comments
Friday, August 22, 2008 1:54 PM
How to create an autonomous transaction in SQL Server 2008
I have been asked by many customers and partners, especially those migrating from Oracle, this question: how to create an autonomous transaction in SQL Server? It turns out to be a tricky thing to do since SQL Server doesn't have built-in autonomous transaction support like Oracle.
An Autonomous transaction is essentially a nested transaction where the inner transaction is not affected by the state of the outer transaction. In other words, you can leave the context of current transaction (outer transaction) and call another transaction (autonomous transaction). Once you finish work in the autonomous transaction, you can come back to continue on within current transaction. What is done in the autonomous transaction is truly DONE and won't be changed no matter what happens to the outer transaction. To make it easier to understand, here is an example of the described scenario.
BEGIN TRAN OuterTran
INSERT TABLE1
BEGIN “AUTONOMOUS” TRAN InnerTran
INSERT TABLE2
COMMIT “AUTONOMOUS” TRAN InnerTran
ROLLBACK TRAN OuterTran
The above pseudo script is meant to preserve result of “INSERT TABLE2”. In SQL Server 2008 or prior versions, "ROLLBACK TRAN" would always rollback all inner transactions to the outermost "BEGIN TRAN" statement (without specifiying savepoint). So the "InnerTran" transaction would be rolled back as well, which is not the desired behavior for the particular scenario.
You could wonder why we need an autonomous transaction in the first place. Why can't we just implement two separate transactions so they don't interfere with each other? There are scenarios where people do need logic structured like this. Logging errors in database is one of the most common scenarios. Below is a TSQL script demonstrating a nested transaction where the inner transaction attempts to save the runtime errors in a table.
USE TEMPDB
GO
CREATE TABLE ErrorLogging (logTime DATETIME, msg VARCHAR(255))
CREATE TABLE TestAT (id INT PRIMARY KEY)
GO
CREATE PROCEDURE usp_ErrorLogging
@errNumber INT
AS
INSERT INTO ErrorLogging VALUES (GETDATE(), 'Error ' + CAST(@errNumber AS VARCHAR(8)) +' occurred.')
GO

DECLARE @ERROR AS INT
INSERT INTO TestAT VALUES (1)
BEGIN TRAN OuterTran
INSERT INTO TestAT VALUES (1) -- This will raise primary key constraint violation error

SELECT @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
BEGIN TRAN InnerTran
EXEC usp_ErrorLogging @ERROR
COMMIT TRAN InnerTran

ROLLBACK TRAN OuterTran
END

IF @@TRANCOUNT > 0
COMMIT TRAN OuterTran
GO
SELECT * FROM TestAT
SELECT * FROM ErrorLogging
GO
If you run above script against SQL Server, you would see no error message recorded in table "ErrorLogging" due to the "ROLLBACK TRAN OuterTran" statement. So, how can we make it work?
In SQL Server 2008, you can implement a loopback linked server to achieve the same goal. For more information about loopback linked server, check Books Online for details (http://msdn.microsoft.com/en-us/library/ms188716.aspx).

USE MASTER
GO
EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
GO
EXEC sp_serveroption loopback,N'remote proc transaction promotion','FALSE'
Go
Note 'remote proc transaction promotion' is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off (FALSE) as we set in the above example, the local transaction will not be promoted to distributed transaction. This is how we are able to separate outer and inner transactions in a "autonomous transaction" fashion.
The Inner transaction above can be replaced by:
BEGIN TRAN InnerTran
EXEC loopback.tempdb.dbo.usp_ErrorLogging @ERROR
COMMIT TRAN InnerTran
Full working script is in the appendix below. I want to point out that this method of using a loopback linked server might not scale well if it's executed very frequently. And it only works in SQL Server 2008 due to new server option of 'remote proc transaction promotion' as discussed above. As always, test before you use it.
If you are looking for alternative ways of creating autonomous transaction on SQL 2008 or 2005, you have these options:
Loopback connection from SQLCLR procedure to start a new transaction. Compared to more rigid structure need of loopback linked server, SQLCLR is more flexible and gives you more control over how you want to handle interaction with database. If the logic of the autonomous transaction includes computational tasks, it's one of SQLCLR's strengths to provide performance gain as extra benefit.
Using table variable to save data within transaction. Table variables are not affected by transaction rollback thus serve as temporary buffer for transaction data. Once transaction is done, you can dump data out of table variable to a permanent table. Table variables have limited scope and are less flexible. Usually they would also be slower due to lack of index/statistics. However, it does offer you a pure TSQL option with no need to create anything new.
Loopback connection from Extended Stored Procedures.
Extended Stored Procedure are on the SQL Server deprecation list and we strongly recommend NOT using it.
In a future blog, I'll provide sample SQLCLR code and a script using a table variable to create autonomous transactions. I will also compare their performance differences with loopback linked server in a scalability test. Stay tuned.
Appendix
USE MASTER
GO
EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
GO
EXEC sp_serveroption loopback,N'remote proc transaction promotion','FALSE'
EXEC sp_serveroption loopback,N'RPC OUT','TRUE' -- Enable RPC to the given server.
Go
USE TEMPDB
GO
CREATE TABLE ErrorLogging (logTime DATETIME, msg VARCHAR(255))
CREATE TABLE TestAT (id INT PRIMARY KEY)
GO
CREATE PROCEDURE usp_ErrorLogging
@errNumber INT
AS
INSERT INTO ErrorLogging VALUES (GETDATE(), 'Error ' + CAST(@errNumber AS VARCHAR(8)) +' occurred.')
GO

DECLARE @ERROR AS INT
INSERT INTO TestAT VALUES (1)
BEGIN TRAN OuterTran
INSERT INTO TestAT VALUES (1) -- This will raise primary key constraint violation error

SELECT @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
BEGIN TRAN InnerTran
EXEC loopback.tempdb.dbo.usp_ErrorLogging @ERROR
COMMIT TRAN InnerTran

ROLLBACK TRAN OuterTran
END

IF @@TRANCOUNT > 0
COMMIT TRAN OuterTran
GO
SELECT * FROM TestAT
SELECT * FROM ErrorLogging
GO Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 2 Comments
Friday, July 11, 2008 1:51 PM
UPDATE with OUTPUT clause – Triggers – and SQLMoreResults

NOTE: the code in this BLOG is TSQL instead of ODBC calls. Since ODBC can be hard to understand and other API’s will have the same basic issues, I decided to use the simpler and more concise TSQL, which should also appeal to a wider audience.

An ISV I work with recently ran into an interesting problem; here is the description and solution.

PROBLEM:
Adding an unexpected trigger caused application code to fail due to incomplete SQL Syntax, and not reading through all returned results.

The ISV wanted to utilize the OUTPUT Clause of the UPDATE statement in their ODBC (SNAC) based application. The OUTPUT clause is very useful in providing data back to the application regarding the row, or rows, which were updated (or: inserted / deleted). In the example I use below, the application is interested in knowing the date/time of the updated row(s).

This could be accomplished by issuing the following statement:
UPDATE T SET COL2 = @Pcol2, COL3 = getdate() OUTPUT CAST(INSERTED.COL3 AS varchar(30))WHERE COL1 = @Pcol1

The ISV coded up the application expecting a return value for number of rows affected, and if that value was greater than 0 then it also returned the value of the inserted date/time.

This worked well, until an external Partner application added a trigger to the table listed in the UPDATE statement.

Example: CREATE TRIGGER [dbo].[TTrigger1] on [dbo].[T] after update as update t2 set col3 = 0

Now the application failed on the UPDATE statement with the following error message:
[Microsoft][SQL Native Client][SQL Server]The target table 'T' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

The error message is self-explanatory, but was a surprise to the ISV application (and the application developer). The developer did not expect a trigger to ever be created on the table.
There are two different methods of getting OUTPUT data from an UPDATE statement;
· UPDATE with the OUTPUT clause only – this returns output results directly as part of the statement. This option cannot have a trigger defined on the table.
· UPDATE with OUTPUT and INTO clauses – this returns the output a specific table, or table variable. This option must be used if there is any possibility the table will have a trigger on it at any point.
· See the following website for complete the OUTPUT Clause documentation:
http://msdn.microsoft.com/en-us/library/ms177564.aspx
The developer then utilized the following syntax to send the same statement to SQL Server, and also to get the expected result back: declare @p165 table (col2 varchar(30));UPDATE T SET COL2 = ?, COL3 = getdate() OUTPUT CAST(INSERTED.COL3 AS varchar(30)) into @p165 WHERE COL1 = 1;select * from @p165

Now a subtlety occurred, can you guess what it was? If you guessed that additional results are returned you are correct.
The ODBC code returned data in a loop utilizing the following API calls: SQLFetch, SQLNumResultCols, SQLRowCount, SQLMoreResults:
· The first results returned were the number of rows affected by the trigger, not the number of rows affected by the UPDATE statement, which was what the application was actually expecting
· The second set of results were the number of rows affected by the UPDATE statement
· The third set of results were the number of rows returned by the SELECT statement reading the table variable
· And finally, the actual data from the updated row(s) – which is what we really wanted in the first place!
So, the lessons to be learned here are:
1. Be aware that triggers will affect your UPDATE statements if utilizing the OUTPUT clause
2. You should utilize the INTO clause to avoid the issue
3. Always use SQLMoreResults to read all of the result-sets that could be returned from SELECT, UPDATE, INSERT, or DELETE statements.
4. Triggers should include the ‘SET NOCOUNT ON’ statement to avoid returning the ‘affected number of rows’.
SOLUTION:
The application was changed to utilize the INTO clause, and SQLMoreResults was used to return all the resulting data. Using SET NOCOUNT ON in trigger logic is also a best practice that prevents additional results ‘Rows affected’ from being generated.

Here is a script to duplicate the issues I’ve described:
USE tempdb
GO
------You may want to run this script in steps from comment – to comment
------so you can follow along, instead of running the entire script at once

CREATE TABLE t(
[col1] [int] NOT NULL,
[col2] [varchar](30) NULL,
[col3] [datetime] NULL
) ON [PRIMARY]
GO
insert into t values (1,'abc', getdate())
insert into t values (1,'abc', getdate())
insert into t values (1,'abc', getdate())
GO
select * from t
GO
UPDATE t SET col2 = 'Peter', col3 = getdate()
OUTPUT CAST(INSERTED.col3 AS varchar(30))WHERE col1 = 1
GO
select * from t
GO
------So far everything is good, Now let’s add the new table and the trigger
CREATE TABLE t2(
[col1] [int] NULL,
[col2] [datetime] NULL
) ON [PRIMARY]
GO
insert into t2 values (2, getdate())
insert into t2 values (2, getdate())
GO
select * from t2
GO
------In this example, the trigger: ttr1 will update the rows
------of a second table: t2
CREATE TRIGGER ttr1 on t after update as update t2 set col1 = 0
GO
------OK, let’s try now with the trigger on
UPDATE t SET col2 = 'Peter', col3 = getdate() OUTPUT CAST(INSERTED.col3 AS varchar(30))WHERE col1 = 1
GO
------Chances are good you got the following error message
--Msg 334, Level 16, State 1, Line 1
--The target table 't' of the DML statement cannot have any enabled triggers --if the statement contains an OUTPUT clause without INTO clause.
----- let’s fix that now.
declare @p1 varchar(30)
UPDATE t SET col2 = 'Peter', col3 = getdate() OUTPUT CAST(INSERTED.col3 AS varchar(30))into @p1 WHERE col1 = 1
GO
------Notice this failed as well with the following error message
--Msg 1087, Level 16, State 1, Line 2
--Must declare the table variable "@p1".
------We need to use a table
------for this to work correctly we must use a table or
------a table variable where the ‘INTO’ data will reside,
------and be retrieved from
declare @p1 table (col2 varchar(30))
UPDATE t SET col2 = 'Peter', col3 = getdate() OUTPUT CAST(INSERTED.col3 AS varchar(30))into @p1 WHERE col1 = 1
select * from @p1
--Now you get what we were originally looking for
-- the date/times of the rows that were updated
--Look at the results under the 'Messages' tab as well...
--you will see the number of rows affected:
-- 2 for the rows inserted as part of the trigger
-- 3 for the rows Updated
-- and 3 for the rows we selected from the table variable
--Now, you can see that the application must utilize SQLMoreResults if it
--wants to return all the valid results.

Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 1 Comments
Thursday, June 26, 2008 10:13 AM
Use SQL Server replay tools to reproduce and resolve customer issues
For many ISVs run that into issues at customer sites, it is sometimes difficult to isolate underlying problems, especially on a 24x7 production environment, where limitations apply to real time troubleshooting and live debugging. In situations like this, constructing a repro scenario in a separate environment would be ideal to minimize impact to live production system, and to speed up resolution process.

SQL Server Profiler
Allow me introduce SQL Profiler, which offers replay trace function. Well, it’s not something new. First shipped in SQL Server 7.0, the feature has gone through many improvements in later releases. You can use the tool to take a captured trace as input and replay it against test database(s). It helps identify issues that could be reproduced by replaying the events in the trace. Profiler itself uses ODBC only. In SQL Server 2005 and 2008, the replay function can be configured to use multiple threads (up to 64) to replay workloads.
Advantages:
1. SQL Server profiler is a built-in tool with full support of Microsoft product team. It works out of box.
2. Easy to set up and run. Capture a trace using predefined replay template with all required events, and replay it against original database(s) (target machine needs to meet certain requirements http://msdn2.microsoft.com/en-us/library/ms175525.aspx)
3. In addition to multi-threaded replay, it also provides option of step through to replay events in the order they were traced.
Disadvantages:
1. Certain events can’t be replayed including replication, events involving GUID, session binding events, operations on Blobs using bcp, full-text, READTEXT, WRITETEXT, and etc. See BOL for more details (http://msdn2.microsoft.com/en-us/library/ms188238.aspx)
2. The tool does not support multiple machine replay (running multiple instances of Profiler from multiple machines to replay the trace).
3. Profiler GUI tool is client side tracing and might be intrusive and generate significant performance overhead when capturing events for replay. Be careful of what events to capture and consider using server side tracing (sp_trace_create).

RML Utilities
Starting in SQL Server 2000, SQL Server Customer Support Services team (CSS) started a project of similar tool, called Ostress, with higher flexibility and scalability to help troubleshoot some of the challenging SQL problems. The latest version is packaged in “Replay Markup Language(RML) Utilities” supporting both SQL 2005 and SQL 2000 (http://support.microsoft.com/kb/944837). The tool can replay multi-threaded events as profiler does but with multiple machine replay support. It can simulate up to 1000 concurrent threads. The tool has a component called Ostress (just like old version), which takes a TSQL batch file and “stress” test it by opening arbitrary number of connections and iterate the TSQL batch in each connection configurable number of loops. This is useful when workload can be characterized as same or similar batch from various number of users (connections).

Advantages:
1. The tool offers both replay and stress test options.
2. It supports multiple machine replay (multiple instances of OStress) with up to 1000 concurrent threads.
3. OStress supports 7.0, 2000, and 2005 trace formats.
Disadvantages:
1. The tool is provided as is, no technical support from Microsoft. But you can submit questions via contact in readme of the tool.
2. Requires extra steps to process trace file and convert to RML format before being replayed.
3. Does not support MARS replay.

Recommendation
When to use SQL profiler and when to use RML Utilities? If you have a workload that can be replayed/reproduced with no or low concurrency requirement (<64> 64 threads) or an isolated batch that can be “stress” tested for simulation, use RML Utilities. Keep in mind, for concurrency replay, full sync of ordered events is very hard to replay and no tools exist today to exactly duplicate the original trace. So the issues that happened on traced source server might not be reproduced consistently afterwards even on same environment.

Both tools above are for database replay. For a simulation test of multi-tier application environment, consider load-test tool of Visual Studio (Team edition) or 3rd-party vendor products.
Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 1 Comments
Tuesday, May 27, 2008 10:49 AM
SQL Server Intermittent Connectivity Issue
Recently many customers of an ISV I work with, reported intermittent connectivity issues when running the ISV application on SQL Server. Some customers reported the issue to be SQL Server 2005 specific. Others stated that they are experiencing the same issue on both SQL Server 2000 and 2005. Due to the intermittent nature, and the variation of the issue, it took us quite a while to collect all the data, (odbc trace, netmon trace, sql trace…), analyse it, and understand the exact cause.
SynAttackProtect
The first issue we found was a subtle Winsock behavior change in Window 2003 SP1. Windows 2003 SP1 introduces a configurable registry setting: SynAttackProtect, that protects the server from network Denial-Of-Service attacks. By default the protection is on. In a SQL Server environment, when the number of simultaneous client connection requests is more than the system can handle and SQL Server backlog queue is full, the client will receive a 'connection failed' error from SQL Server:
TCP Provider: An existing connection was forcibly closed by the remote host
The SQL Protocols team has a good Blog that explains the detailed interaction between SynAttackProtect setting and SQL Server. See http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx.
In Windows 2003, this issue could be worked-around by configuring the registry setting to disable SynAttackProtect.
1) Launch regedit.exe
2) Add DWORD value named SynAttackProtect under registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\
3) Set data value to 0
See http://technet2.microsoft.com/windowsserver/en/library/8d3a9f4d-13d1-4280-ac57-30242504d8ba1033.mspx?mfr=true for additional information.
After applying the registry change, two customers reported the intermittent connection issue went away. Both customers had been stress-testing SQL Server. SynAttackProtect is more likely to become an issue in a lab environment where SQL Server is being stress-tested with extreme load. Customers running stress-testing in lab environments should turn off SynAttackProtect. I am not sure I’d recommend proactively turning it off in a production system given the potential security risk. If a production system ever runs into the SynAttackProtect issue, the where/why of the large number of connection requests should be examined first.
Windows “Scalable Networking Pack”
Windows Scalable Networking Pack was a second network stack change that was released as part of Windows 2003 SP1 + KB91222, or Windows 2003 SP2. With the Scalable Networking Pack, the TCP Chimney Offload feature is enabled by default to increase performance. However implementations on certain network cards are problematic when TCP Chimney Offload enabled, and can cause intermittent connection drop. When the connection is dropped due to incompatibility between the network card and Windows Scalable Networking Pack, typical error message is
[08S01] [Microsoft][SQL Native Client]Communication link failure
A workaround for this issue could be to disable to TCP Chimney Offload feature.
1) Launch regedit.exe
2) Edit DWORD EnableTCPChimney under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
3) Edit DWORD EnableRSS under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
4) Edit DWORD EnableTCPA under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
5) Restart the server
Amongst others, network cards using Broadcom 5708 chipsets are known to have compatibility issues w/ TCP Chimney Offload. Disabling TCP Chimney Offload would fix the incompatibility issue. However it would also remove the benefit of Windows TCP stack performance improvements. It should only be used as a workaround until a fix becomes available from the network card vendor.
See support article http://support.microsoft.com/kb/942861 for additional information.
By now, the majority of customers solved the intermittent connectivity issue after applying the SynAttackProtect and/or TCP Chimney Offload changes. Some customers connecting to SQL Server through Citrix have to turn off the TCP Chimney Offload feature on the Citrix server to fix the issue.
Query Timeout
The particular ISV application runs with a configurable Query timeout, by default the timeout is set to 1 second. When a query timeout happens, the application will retry the query for 16 times, if it still fails, the query will be submitted again with nolock hint. How would that affect the connection? At the first glance, it seems to be irrelevant, but it does. Here’s the sequence of what could happen.
1) The query is submitted to SNAC for execution
2) The execution of the query takes too long so the timeout expires
3) Once the timeout happens, client attempts to cancel the query and sends an ATTN packet to the server. After sending ATTN, the client then waits for the response from the server. A timeout is set for this wait, the value of the timeout is obtained through SQL_ATTR_CONNECT_TIMEOUT and if not set, the default to 120 seconds.
4) The wait for server to response also times out. This is treated as an error from the client and basically the connection is considered dead in such scenarios
5) The client marks the connection as dead and then returns the “Query timeout expired” error message.
6) The application code, on seeing a timeout expired message attempts to execute the request again on the same connection, but immediately hits the “communication link failure” message because the connection is deemed as dead by the client

During the investigation, we discovered a regression in SQL Server 2005. SQL Server 2005 may not response to a query cancel (timeout) request in a timely manner if the query requires index scan through a large number of pages. Checking for any attention requests is delayed when the SQL Server storage engine is busy bringing pages in from disk. Resulting in
[08S01] [Microsoft][SQL Native Client]Communication link failure

A hotfix is available to fix the regression, see details in
http://support.microsoft.com/kb/945442.

All the remaining customers had some long running batch queries that index scan a large table. This is just the last piece we needed to complete the puzzle. After applying the hotfix, all customers reported the problem solved.
Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 2 Comments
Tuesday, March 18, 2008 4:25 PM
Using time zone data in SQL Server 2008

In SQL Server 2008 Microsoft has introduced a number of new date and time data types. One of these is the datetimeoffset data type. This data type includes an offset from UTC time as well as the datetime value and ensures that the datetime can be retrieved in UTC or a particular timezone based on this offset. There are also new functions to allow for conversions between different time zones using the new function SWITCHOFFSET().

An example from SQL Server 2008 Books On Line (BOL):
CREATE TABLE dbo.test
(
ColDatetimeoffset datetimeoffset
);
GO
INSERT INTO dbo.test
VALUES ('1998-09-20 7:45:50.71345 -5:00');
GO
SELECT SWITCHOFFSET (ColDatetimeoffset, '-08:00')
FROM dbo.test;
GO
--Returns: 1998-09-20 04:45:50.7134500 -08:00
SELECT ColDatetimeoffset
FROM dbo.test;
--Returns: 1998-09-20 07:45:50.7134500 -05:00

One of the most common questions we are asked is why we use the offset and not a timezone name. A timezone name is much easier to remember than an offset, and most people do not know an offset without looking it up, making queries more difficult.

Unfortunately, there is no current international standard authority for timezone names and values. Each system needs to use a system of their own choosing, and until there is an international standard, it is not feasible to try to have SQL Server provide one, and would ultimately cause more problems than it would solve. However, there are a couple of common systems that are well recognized around the world. One is the Dynamic timezone data that is stored in the Windows Vista registry. This data can be read from the registry into a file, which is then imported into SQL Server.
Another cross-platform standard is the public domain Olson Timezone database (http://www.twinsun.com/tz/tz-link.htm). There are many public domain programs for extracting a time zone from these files, but at this time most are programmatic solutions. So a programmatic function could be written in the CLR, but to provide full database functionality and query-ability, a table is needed.

The programmatic solutions take a date, then apply the many different rules that determine when a zone is in daylight savings time and when it is not. However, there are also historical changes. Daylight savings time changed in 2007, meaning that determining what the offset for a particular zone is at a particular time is different depending on the year. Then there are times when leap seconds need to be added. Therefore any data-driven solution must have rows that have valid time ranges as well.

The approach recommended here is to take one of the DLLs found on the web and instead of providing a programmatic solution around a specific date – to write all of the rows out into a database as an offset with the valid ranges. Currently this example uses the Windows standard naming conventions for timezones, with a mapping to the Olson timezone names, but you could easily add other names in other languages as well.

Working with timezones is very complex, and the following is a suggestion only for some ideas on how to use time zone data more effectively. This is an example program (no guarantees) that uses a .NET library and writes the data from the Olson tz files in table format, and which can then be imported into SQL Server. The .NET timezone library can be found at http://www.babiej.demon.nl/Tz4Net/main.htm and they request a small donation.

Here is some sample code to write to files the timezone data (no guarantees – does not include leap seconds):

StreamWriter sr = File.CreateText(@"D:\TZMapping.txt");
StreamWriter tr = File.CreateText(@"D:\TZZones.txt");

string[] zoneNames = OlsonTimeZone.AllNames;
sr.WriteLine("ID\tDaylightName\tStandardName\tRawUtcOffset\tOffsetSeconds\tWin32Id");
tr.WriteLine("ID\tTransitionStart\tTransitionEnd\tDeltaSeconds\tDST");

for (int i = 0; i < zoneNames.Length; i++)
{
OlsonTimeZone tz = OlsonTimeZone.GetInstanceFromOlsonName(zoneNames[i].ToString());
sr.Write(i.ToString() + "\t");
sr.Write(tz.DaylightName.Trim() + "\t");
sr.Write(tz.StandardName.Trim() + "\t");
sr.Write(tz.RawUtcOffset.ToString() + "\t");
sr.Write(tz.RawUtcOffset.TotalSeconds.ToString() + "\t");
sr.WriteLine(tz.Win32Id == null ? "" : tz.Win32Id.Trim());

DaylightTime[] times = tz.AllTimeChanges;
for (int j = 0; j < times.Length; j++)
{
tr.Write(i.ToString() + "\t");
tr.Write(times[j].Start.ToString("yyyy-MM-dd HH:mm:ss") + "\t");
tr.Write(times[j].End.ToString("yyyy-MM-dd HH:mm:ss") + "\t");
tr.Write(times[j] is StandardTime ? "0\t" :times[j].Delta.TotalSeconds.ToString() + "\t");
tr.WriteLine(times[j] is StandardTime ? false.ToString() : true.ToString() );
}
}
tr.WriteLine();
sr.WriteLine();
tr.Close();
sr.Close();

Import the TZMapping file, which will become the parent table, with the ID as the primary key. Your table structure might look like this:

Please note: If you use the Flat File Datasource in the Import Data Wizard in SQL Server 2008 Management Studio, you will need to open the Advanced Tab to set the source OutPutColumnWidth to greater than the default of 50. Then import the TZZones file, which will become the child table with the ID, TransitionStart, and TransitionEnd as the composite primary key with a foreign key reference to the TZMapping table. The TZZones table includes historical timezone data. Joining these new tables into your data into queries now allows for queries that include standard names, Windows IDs, etc.

For example, offsets can now be retrieved by a preferred name:

select UtcOffset from TZmapping where StandardName = 'US/Pacific (PST)'

The following two queries return different offset amounts for the same day in two different years. This is because the US changed daylight savings time, and the date in March now falls into daylight savings when it did not before.

select (dbo.TZMapping.OffsetSeconds + dbo.TZZones.DeltaSeconds)/3600
from dbo.TZMapping
join dbo.TZZones
on dbo.TZMapping.id = dbo.TZZones.id
where dbo.TZMapping.StandardName = 'America/Los_Angeles (PST)'
and '2006-03-15'
between dbo.TZZones.TransitionStart
and dbo.TZZones.TransitionEnd

select (dbo.TZMapping.OffsetSeconds + dbo.TZZones.DeltaSeconds)/3600
from dbo.TZMapping
join dbo.TZZones
on dbo.TZMapping.id = dbo.TZZones.id
where dbo.TZMapping.StandardName = 'America/Los_Angeles (PST)'
and '2007-03-15'
between dbo.TZZones.TransitionStart
and dbo.TZZones.TransitionEnd

Again, timezones are a complex area and each application will need to address how you are going to handle time zone data to make programs more user friendly. This is just one small example.
Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 5 Comments
Tuesday, March 18, 2008 3:49 PM
Increase your SQL Server performance by replacing cursors with set operations

You have probably heard many times, from different sources, that as a best practice; avoid using TSQL cursors.
During a recent visit to a partner we ran into a common cursor case, which I wanted to use as an example to demonstrate why you should avoid TSQL cursors in most cases, and how to convert cursor logic to simple set join operations. Now there are certain scenarios where using a cursor makes sense. For example, a cursor is ideal for row by row processing that can’t be accomplished by set based operations. A cursor is flexible in that it provides a window, or subset, of data and that allows manipulation of the data in various ways. Study carefully what you want to achieve on case by case basis before using a cursor. Keep in mind SQL Server, as a modern RDBMS system, performs much better with set operations.
Here is simplified version of a real cursor that was used to update a big table with over 200 million rows.
DECLARE @EntityId Varchar(16)
DECLARE @PerfId Varchar(16)
DECLARE @BaseId Varchar(16)
DECLARE @UpdateStatus Int

DECLARE outerCursor CURSOR FOR
SELECT EntityId, BaseId
FROM outerTable
--Returns 204,000 rows

OPEN outerCursor
FETCH NEXT FROM outerCursor INTO @EntityId, @BaseId

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE innerCursor CURSOR FOR
SELECT PRFMR_ID
FROM innerTable
WHERE ENTY_ID = @BaseId

OPEN innerCursor
FETCH NEXT FROM innerCursor INTO @PerfId
SET @UpdateStatus = @@FETCH_STATUS

WHILE @UpdateStatus = 0
BEGIN
UPDATE 200MilRowTable
SET ENTY_ID = @EntityId
WHERE PRFMR_ID = @PerfId

FETCH NEXT FROM innerCursor INTO @PerfId
SET @UpdateStatus = @@FETCH_STATUS
END

CLOSE innerCursor
DEALLOCATE innerCursor --clean up inner cursor

FETCH NEXT FROM outerCursor INTO @EntityId, @BaseId
END

CLOSE outerCursor
DEALLOCATE outerCursor –cleanup outer cursor

You might notice that this is a nested cursor with 204,000 loops in total for outerCursor. The innerTable has 10 million rows but innerCursor varies in number of loops depending on @BaseId of outerCursor. When I arrived at the customer this cursor had been running for over a day. The developer was “hoping” that it would finish soon given another day or two. The problem was nobody knew for sure how much time this thing would need to complete. Well, we can find out how much progress it has made so far to make an educated guess:
SELECT execution_count, st.text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE st.text like '%FETCH NEXT FROM outerCursor INTO%'

This would tell us how many times the outer cursor has looped already. It returned 107. That’s only around 0.5% (107/204,000) after 30 hours of running. If the trend were to continue, the cursor would need another 8+ months!!!
A rewrite of the batch to take advantage of set operations is as simple as this:
SELECT i.PRFMR_ID, o.EntityId INTO #tempTable
FROM innerTable i join outerTable o on i.ENTY_ID = o.BaseId
Go
UPDATE 200MilRowTable
SET m.ENTY_ID = t.EntityId
FROM 200MilRowTable m join #tempTable t on m.PRFMR_ID = t.PRFMR_ID
Go
--note this is only one of a few ways to rewrite.
In this particular case, “SELECT INTO” is minimally logged under simple recovery mode. The two statement approach makes it easier to understand the conversion logic.
This batch took approximately 17 hours to complete. Between the statement, I also put the database into simple recovery mode and added appropriate indexes to the temp table. I also dropped indexes from 200MilRowTable that touched “ENTY_ID” to speed this up. Adding indexes back took another 7 hours. The total time was approximately 24 hours, which is just a small fraction of the original cursor batch. I need to point out that the non-cursor batch uses more resources since the UPDATE now spawns multiple threads to process parallely. Remember our goal here is to make this finish faster not worrying about how much resources it consumes.
Note: this might not be a perfect example because the nested cursor is magnifying the slow performance.
However, the bottom line is; aviod cursors if possible and use joins / set operations whenever you can.
Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 2 Comments
Thursday, March 06, 2008 11:33 AM
Appending Data Using SQL 2008 Filestream
SQL Server 2008 has a new feature called Filestream, which allows you to save large binary files in the file system, instead of in the database. This is targeted directly at the scenario that many document management and web applications have today where they save some metadata in the database, with a column holding the path to the actual file. However, there is no transactional context between the two; therefore, the data in the database can be changed to no longer point to the actual location, or the file moved without the pointer being updated. Users want to be able to have documents, pictures, video, etc. in the file system with the streaming support the file system provides, but have the metadata be consistent. This is exactly what the Filestream feature is targeted for.

When a large binary files gets ‘updated’ most users/applications will replace the file in its entirety (you do not usually update part of an image through the database). Filestream does not currently support in-place updates. Therefore an update to a column with the filestream attribute is implemented by creating a new zero-byte file, which then has the entire new data value written to it. When the update is committed, the file pointer is then changed to point to the new file, leaving the old file to be deleted at garbage collection time. This happens at a checkpoint for simple recovery, and at a backup or log backup.

Code that updates large value data types (varchar(max), nvarchar(max), and varbinary(max)) may use the .Write(offset) UPDATE clause; however since an in-place update of a column with the Filestream attribute is not supported, the .Write(offset) UPDATE clause will error out. Therefore, even though filestream is only a storage attribute, the update code does not work against both systems, requiring knowledge of underlying storage mechanism.

Many of the update actions taken against large data types are actually appends. And many logging scenarios continuously append to an ever-growing file. Filestream offers an option for this case, to avoid the scenario where the original data is pulled to the client, data appended, and then a new file written with the combined data – leaving the old file to be garbage collected later.

In order to avoid this round trip, a device FS control (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT) can be issued to trigger a server-side copy of the old content. This avoids the data moving over the network. This control is not yet documented; note that the DeviceIoControl ControlCode constant that is needed is: 599392. This will be added to the Filestream API documentation at a future date.

Example code:
[DllImport(“kernel32.dll”, SetLastError = true)]
Static extern bool DeviceIoControl(
IntPtr hDevice,
Uint dwIoControlCode,
IntPter lpInBuffer,
Uint ninBufferSize,
[out] IntPtr lpOutBuffer,
Uiny nOutBufferSize,
Ref uint lpBytesReturned,
IntPtr lpOverlapped );

IntPtr hFile = IntPtr.Zero;
Uint bytesReturned;
// hFile = mySafeHafle.DangereousGetHandle(); or something similar

Bool result = DeviceIoControl ( hFile, 599392, IntPtr.Zero, 0, IntPtr.Zero, 0, ref lpBytesReturned, InPtr.Zero );

As Filestream is minimally logged, if there is no activity other than filestream activity, there is usually not enough activity to trigger a checkpoint, and the old files will not get deleted. In non-simple recovery mode, the log will not increase, but the db will grow until those files get garbage collected. This will occur in a background thread, and can impact the server through this activity.

Therefore, it is a best practice to manually issue that checkpoint if in simple recovery mode; and to maintain an optimal transaction log backup to avoid the file growth.

Other quick notes:

Filestream deletes are significantly faster than blob deletes.
If an application needs to commonly delete large data values, filestream will be more scalable as the metadata is changed, then the file garbage collected asynchronously. For instance, the delete of a 6GB nvarbinary(max) file that took 6 seconds, happens in milliseconds with a filestream delete.

Filestream maximum size is different for SNAC 9 and SNAC 10.
Files using SNAC9 have a maximum size of 2GB. SNAC 10 supports unlimited size.

Note: Altering an existing column without a filestream attribute to having the filestream attribute is not supported.
This means that moving data from a blob storage mechanism to filestream mechanism requires that the data be copied to the new datatype with the filestream attribute.

Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 1 Comments
Monday, February 25, 2008 10:57 AM
Now is time to try SQL Server 2008
Did you know that SQL Server 2008 is just around the corner, and that the SQL development team has just publicly released the latest Community Technology Preview (CTP 6) version?
This latest beta version of SQL 2008 includes many exciting new features like filtered indexes, sparse columns, data compression, Microsoft sync, integrated full text search, Report rendering in Microsoft Word, Data visualization enhancements, extensive Books Online documentation, the first feature pack, and many, many more.
Now is great time to try it for yourself! Download SQL Server 2008 here .
Also, the SQLCAT teams (including us on the SQLCAT ISV team) have been busy working with our customers and partners implementing pre-release (CTP) builds. We have many lessons learned to share with you; these can be found here
Additionally, we have a number of great best practices papers coming up:
· Index Defragmentation Best Practices
· Best Practices for using Resource Governor with a mixed (OLTP and Reporting) workload
· Data Compression: Design and Implementation Best Practices, Performance Impact
· XEvents Toolkit
· SQL 2008 Usage-Based Optimization Efficacy (subject to name change)
· Scale-Out Querying with Analysis Services 2008
· Best practices for Data Warehousing in SQL Server 2008

Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 1 Comments
Friday, June 29, 2007 8:56 AM
Detecting Overlapping Indexes in SQL Server 2005

When SQL Server has an optimal index that satisfies the search predicates of a query the optimizer performs an index SEEK operation as opposed to an index (or table) scan to retrieve the required rows; this is desirable. Based on this, one may be led to believe that having an index for every possible query predicate set would result in all the queries executing optimally. While true, one has to keep in mind that the indexes need to be maintained when the underlying table data in the column included in the index changes, which amounts to overhead for the database engine. So as you may guess, there are advantages of having indexes, but having too many can result in excessive overhead. This implies that you need to carefully evaluate the pros and cons before creating indexes.

In your first evaluation scenario you clearly want to avoid the case of having overlapping indexes as there is no additional value that an overlapping index provides. For example, consider a table ‘TabA’ and its three associated indexes created with the following definitions.

CREATE TABLE TabA
( Col1 INT, Col2 INT, Col3 INT, Col4 INT );
GO

CREATE INDEX idx1 ON TabA ( Col1, Col2, Col3 );
CREATE INDEX idx2 ON TabA ( Col1, Col2 );
CREATE INDEX idx3 ON TabA ( Col1 DESC, Col2 DESC );
GO

In the table structure above, the index idx1 is a superset (overlap) of the index idx2 and therefore redundant. As can be expected any query that needs to perform a search on Col1 and Col2 could use index idx1 just as well as the index idx2 as seen in the graphical query plan below.

Such overlapping indexes are often a result of multiple developers working on the same product and not evaluating and understanding the existing schema before making additions. Once created, detecting such overlapping indexes in a database can often be a laborious task requiring detailed analysis. More importantly, most DBAs do not like to disable or drop indexes because they are not certain of the queries they were created to help with and fear the side effects the action may have.

The script below uses the new system catalog introduced in SQL Server 2005 to report all duplicate indexes in the current database context.

CREATE FUNCTION dbo.INDEX_COL_PROPERTIES (@TabName nvarchar(128), @IndexId INT, @ColId INT)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @IsDescending INT;
SELECT @IsDescending = is_descending_key
FROM sys.index_columns SYSIDXCOLS
WHERE OBJECT_ID(@TabName) = SYSIDXCOLS.object_id
AND @IndexId = SYSIDXCOLS.index_id
AND @ColId = SYSIDXCOLS.key_ordinal;

-- Return the value of @IsDescending as the property
RETURN(@IsDescending);
END;
GO

-- Find Duplicate Indexes in SQL Server Database
CREATE VIEW IndexList_VW AS
SELECT
SYSSCH.[name] AS SchemaName,
SYSOBJ.[name] AS TableName,
SYSIDX.[name] AS IndexName,
SYSIDX.[is_unique] AS IndexIsUnique,
SYSIDX.[type_desc] AS IndexType,
SYSIDX.[is_disabled] AS IsDisabled,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 1 ) AS Column1,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 2 ) AS Column2,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 3 ) AS Column3,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 4 ) AS Column4,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 5 ) AS Column5,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 6 ) AS Column6,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 7 ) AS Column7,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 8 ) AS Column8,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 9 ) AS Column9,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 10 ) AS Column10,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 11 ) AS Column11,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 12 ) AS Column12,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 13 ) AS Column13,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 14 ) AS Column14,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 15 ) AS Column15,
INDEX_COL( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 16 ) AS Column16,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 1 ) AS Column1_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 2 ) AS Column2_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 3 ) AS Column3_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 4 ) AS Column4_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 5 ) AS Column5_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 6 ) AS Column6_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 7 ) AS Column7_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 8 ) AS Column8_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 9 ) AS Column9_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 10 ) AS Column10_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 11 ) AS Column11_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 12 ) AS Column12_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 13 ) AS Column13_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 14 ) AS Column14_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 15 ) AS Column15_Prop,
dbo.INDEX_COL_PROPERTIES( SCHEMA_NAME(SYSOBJ.SCHEMA_ID)+'.'+SYSOBJ.[name], SYSIDX.index_id, 16 ) AS Column16_Prop
FROM sys.indexes SYSIDX INNER JOIN sys.objects SYSOBJ ON SYSIDX.[object_id] = SYSOBJ.[object_id]
INNER JOIN sys.schemas SYSSCH ON SYSOBJ.schema_id = SYSSCH.schema_id
WHERE SYSIDX.index_id > 0
AND INDEXPROPERTY(SYSOBJ.[object_id], SYSIDX.[name], 'IsStatistics') = 0
GO

SELECT
vwA.SchemaName,
vwA.TableName,
vwA.IndexIsUnique,
vwA.IndexType,
vwA.IndexName,
vwB.IndexName AS OverlappingIndex,
vwA.Column1, vwA.Column2, vwA.Column3, vwA.Column4, vwA.Column5, vwA.Column6, vwA.Column7, vwA.Column8,
vwA.Column9, vwA.Column10, vwA.Column11, vwA.Column12, vwA.Column13, vwA.Column14, vwA.Column15, vwA.Column16
FROM IndexList_VW vwA INNER JOIN IndexList_VW vwB ON vwA.TableName=vwB.TableName
AND vwA.IndexName <> vwB.IndexName
AND vwA.IsDisabled = 0
AND vwB.IsDisabled = 0
AND (vwA.Column1=vwB.Column1 AND vwA.Column1_Prop=vwB.Column1_Prop)
AND ((vwA.Column2=vwB.Column2 AND vwA.Column2_Prop=vwB.Column2_Prop) OR vwA.Column2 IS NULL OR vwB.Column2 IS NULL)
AND ((vwA.Column3=vwB.Column3 AND vwA.Column3_Prop=vwB.Column3_Prop) OR vwA.Column3 IS NULL OR vwB.Column3 IS NULL)
AND ((vwA.Column4=vwB.Column4 AND vwA.Column4_Prop=vwB.Column4_Prop) OR vwA.Column4 IS NULL OR vwB.Column4 IS NULL)
AND ((vwA.Column5=vwB.Column5 AND vwA.Column5_Prop=vwB.Column5_Prop) OR vwA.Column5 IS NULL OR vwB.Column5 IS NULL)
AND ((vwA.Column6=vwB.Column6 AND vwA.Column6_Prop=vwB.Column6_Prop) OR vwA.Column6 IS NULL OR vwB.Column6 IS NULL)
AND ((vwA.Column7=vwB.Column7 AND vwA.Column7_Prop=vwB.Column7_Prop) OR vwA.Column7 IS NULL OR vwB.Column7 IS NULL)
AND ((vwA.Column8=vwB.Column8 AND vwA.Column8_Prop=vwB.Column8_Prop) OR vwA.Column8 IS NULL OR vwB.Column8 IS NULL)
AND ((vwA.Column9=vwB.Column9 AND vwA.Column9_Prop=vwB.Column9_Prop) OR vwA.Column9 IS NULL OR vwB.Column9 IS NULL)
AND ((vwA.Column10=vwB.Column10 AND vwA.Column10_Prop=vwB.Column10_Prop) OR vwA.Column10 IS NULL OR vwB.Column10 IS NULL)
AND ((vwA.Column11=vwB.Column11 AND vwA.Column11_Prop=vwB.Column11_Prop) OR vwA.Column11 IS NULL OR vwB.Column11 IS NULL)
AND ((vwA.Column12=vwB.Column12 AND vwA.Column12_Prop=vwB.Column12_Prop) OR vwA.Column12 IS NULL OR vwB.Column12 IS NULL)
AND ((vwA.Column13=vwB.Column13 AND vwA.Column13_Prop=vwB.Column13_Prop) OR vwA.Column13 IS NULL OR vwB.Column13 IS NULL)
AND ((vwA.Column14=vwB.Column14 AND vwA.Column14_Prop=vwB.Column14_Prop) OR vwA.Column14 IS NULL OR vwB.Column14 IS NULL)
AND ((vwA.Column15=vwB.Column15 AND vwA.Column15_Prop=vwB.Column15_Prop) OR vwA.Column15 IS NULL OR vwB.Column15 IS NULL)
AND ((vwA.Column16=vwB.Column16 AND vwA.Column16_Prop=vwB.Column16_Prop) OR vwA.Column16 IS NULL OR vwB.Column16 IS NULL)
ORDER BY
vwA.TableName, vwA.IndexName
GO

-- Drop function and view created above.
DROP FUNCTION dbo.INDEX_COL_PROPERTIES
GO
DROP VIEW IndexList_VW
GO
Executing the script in a test database with the table (TabA) mentioned above produces output similar to the following.

Indexes idx1 and idx2 are reported to have an overlap, and in this case the subset index that has fewer columns (idx2) can be disabled without any negative side-effects (provided there are no Index hints pointing to this index. This is explained in more detail below). This will result in the same performance for select statements but eliminate the overhead of the database engine having to maintain 2 indexes that serve the same general purpose. This is particularly important if the underlying columns (Col1 and Col2) are highly volatile.

A couple of additional points to keep in mind when using this script:
- Overlapping indexes are only those that have the columns in the same order. For example an index created on Col1, Col2, Col3 (in that order) does not overlap with an index created on Col2, Col1, Col3, even though the columns included are the same
- Indexes created on the same set of columns with the same column order, but with different sort order specifications (ASC, DESC) are distinct indexes and are not overlapping
- Indexes with included columns introduced in SQL Server 2005 are correctly reported as overlapping by the above script. For example, an index created as Col1, Col2 and Col3 as an included column overlaps with the index created on Col1, Col2, Col3. For overlapping indexes that involve included columns it is imperative to disable or drop the index with the higher number of included columns (refer to: http://msdn2.microsoft.com/en-us/library/ms188783(SQL.90).aspx for more information about indexes with included columns)
- Clustered indexes and non-clustered indexes are clearly marked in the script output. If a clustered index is reported as overlapped and you plan to drop it, you may want to take some additional steps to make sure that the overlapping index, or some other index, is created as clustered, because it is a SQL Server best practice to have a clustered index on every table (refer to: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx for more information)
- It is recommended to either disable a duplicate index or save its definition before dropping it, in order to be able to recreate it if necessary
- Before disabling or dropping an index you should make sure that there are no index hints in your application that explicitly use the index being disabled, for example: SELECT * FROM TabA WITH (INDEX (idx2)) WHERE col1 = 28. Disabling or dropping an index that is explicitly used via an index hint will result in the query execution failing with an error message 308 or 315.

Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 2 Comments
Tuesday, June 05, 2007 12:47 PM
New SQL Best Practice Articles now available

Please have a look at four new Best Practices Articles
SQL Server 2005 Predeployment I/O best practices http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx by SQLISVPM team member Mike Ruthruff
SQL Server 2005 Deployment Guidance for Web Hosting Environments http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/SQL2005DGWHE.mspxby Alex DeNeui
Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarioshttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspxby Carl Rabeler
Implementing Application Failover with Database Mirroringhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspxby Michael Thomassy, Sanjay Mishra
Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 1 Comments
Monday, April 30, 2007 5:25 PM
Will 64-bit increase the performance of my SQL Server application?
With 64-bit servers becoming more mainstream and SQL Server customers running more deployments on native 64-bit Windows there is a common misperception that running on 64-bit will always provide an increase in performance over 32-bit. Many customers are surprised to find that some workloads actually run faster on 32-bit SQL Server than on the native 64-bit version. The assumption that 64-bit will always outperform 32-bit is not true and is determined largely by characteristics of the particular workload and hardware. Workloads that have a large volume of small uniform transactions using relatively simple query plans (OLTP-like in nature) which are not experiencing memory pressure may not benefit from running on native 64-bit SQL Server.
Differences between 32-bit and 64-bit
There are differences between 32-bit and 64-bit processes in general which apply to SQL Server. The working set of 64-bit SQL Server is larger than 32-bit both in code and data; mainly due to doubling the size for pointers, 64-bit instructions, structures that are cacheline aligned and more. This means 64-bit will be more sensitive to the size of onboard chip caches which may be used less efficiently when compared to 32-bit, resulting in a higher Cost per Instruction (CPI). The end result is applications which are not memory constrained on 32-bit (especially OLTP) may incur a performance penalty when running on 64-bit using the same hardware. We have observed approximately 10-15% degradation in most cases however the impact will depend on the characteristics of the workload and hardware being used.
Which applications will benefit most from 64-bit?
Many SQL Server applications will realize significant performance benefit running on native 64-bit. This platform will also provide much better scalability in the long term so it is important not to be short sighted when evaluating minor performance differences between the two platforms. Native 64-bit SQL Server typically benefits memory hungry applications the most and also applications requiring memory for items other than data pages. Some of these include:
• Procedure Cache: Lots of stored procedures and dynamic SQL batches (OLTP)

• Workspace Memory: Large number of concurrent hash joins or group by’s; large-scale sorts and index builds (Data Warehouse)

• Connection memory: Large numbers of connections

• Thread memory: High Concurrency OLTP, Complex query plans on multi-CPU machines

• CLR GC Heap memory: CLR SP allocations

• Lock Memory: Large scale OLTP
SQL Server 32-bit version allows use of memory beyond the 2 or 3 GB (32-bit limitation of user mode virtual address space) through the use of Address Windowing Extensions however there are restrictions on the use of AWE mapped memory. Specifically, the items above could not utilize AWE mapped memory (more on this below).
What to consider when deciding between 32-bit and 64-bit
When considering one platform over the other I would recommend thinking about the following:
1. Processor architecture plays a significant role. The processor used will impact the overhead introduced by running native 64-bit SQL Server on 64-bit Windows; specifically the amount of cache on the processor. Older generation processors with less L2/L3 cache will likely incur more of a penalty running native 64-bit SQL Server than will newer processors with larger amounts of cache. It is important to consider this and when possible favor chipsets that offer more cache. The performance gain offered by processors with larger cache sizes vary and can only be determined through testing. An exception to this rule would be AMD processors which have L2 cache but no L3 cache. The memory architecture of AMD processors is very different from Intel processors and the lack of L3 cache may be offset by the different architecture of the data transfer between processor and memory. It is important to note that we are not endorsing one processor over the other; we have observed excellent SQL Server performance on both Intel and AMD processors.
2. Memory latency will have a significant impact. The speed of data transfer between processors and memory will have a significant impact on performance. When accessing main memory latency is important for performance. Slower transfer speeds will result in higher latencies and a bigger performance penalty for cache misses. Faster transfer speed will have a positive, and often, significant impact on the performance of 64-bit SQL Server. Reducing memory latency is a key design point and strength of Non-Uniform Memory Access (NUMA) server architectures.
3. Consider the specifics of your particular workload. If your workload is running on 32-bit and is not experiencing memory pressure or does not have any of the requirements mentioned above then the workload will likely not benefit from 64-bit.
4. Look for unexpected behaviors when testing. We have seen a number of cases where moving to 64-bit resulted in unexpected behavior as a result of removing the virtual address space limitations. On 32-bit systems it is common to run with AWE enabled to allow SQL Server to utilize memory beyond 2 or 3 GB. This memory however can only be used for data cache. On 64-bit this restriction is lifted and non-data cache memory can grow much larger than was possible on 32-bit. This includes items such as procedure cache, lock memory, connection memory, workspace memory, etc… While beneficial in most cases there are also cases where this can expose issues with the application’s use of SQL Server. Some common scenarios include:
• Extensive growth of the procedure cache as a result of a high volume of non-parameterized SQL statements. Service Pack 2 for SQL 2005 has some optimizations to help address these issues but it is considered a best practice to parameterize queries when possible and the sp2 optimizations do not replace the need to follow this advice.
• Failure to put a limit on ‘max server memory’ resulting in system wide memory pressure. It is important on 64-bit systems to place an upper limit on ‘max server memory’ leaving sufficient room for the operating system and ensure the SQL Server service account is granted the ‘Lock pages in memory’ security privilege (On 64-bit SQL Server the lock pages in memory privilege is only valid for Enterprise edition). The goal of setting an upper limit is to ensure that there is sufficient memory for the OS, other applications on the machine and allocations within SQL Server that are satisfied from outside the buffer pool. SQL Server’s buffer pool is dynamic and will adjust to external memory pressure however being proactive is always a good strategy. It is not uncommon on systems with large amount of physical memory (>= 16GB) to leave several GB for these items. Refer to http://support.microsoft.com/kb/918483 for more information.
5. Think long term. Although there are times when a workload will perform slightly slower on native 64-bit the difference is most cases is not significant (usually 10% or less). It is important not to let a small impact to performance overshadow the long term benefits of 64-bit SQL Server. Native 64-bit will offer significantly better scalability than 32-bit and is the future of computing (especially for database applications). Even if a workload does not require or benefit from the additional virtual address space now it may in the future.
6. Monitor and Test. By monitoring current performance of your application you may be able to get an idea of whether or not your specific workload will benefit from 64-bit SQL Server. Some potential indicators of memory pressure which may indicate a scenario when 64-bit will help are:
• High rate of I/O coupled with low page life expectancy and/or constant lazy writer activity.
• Constant non-zero values for memory grants pending.
• High rate of temporary objects being created/destroyed.
• High value for stolen pages as a percentage of the total pages consumed by the SQL Server buffer pool.
Ultimately however the only way to definitely know whether or not your application will benefit from running native 64-bit is to test.

Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 3 Comments
Friday, March 23, 2007 8:36 AM
Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting.
Upgrading to SQL Server 2005 has many benefits . As we look back at the past year and the deployment of SQL Server 2005 with our ISV partners, one important but under-advertised feature in SQL Server 2005 became increasingly visible; the ability to control the dynamic name resolution behavior. This capability applies to workloads where object names are NOT fully-qualified with a two or a three part name (for example: “database.schema.object”). If you already fully qualify your object references, which is a SQL Server best practice, then this blog does not apply to you.

In this post, I will first explain the behavior in SQL Server 2000 and expand on what changed in SQL Server 2005. Then I’ll point out how to identify the issue in your workloads and how to change the DEFAULT_SCHEMA setting to take advantage of the benefits.

Dynamic Name Resolution - SQL Server 2000 vs. 2005
A query such as "select * from table1" in SQL Server 2000 goes through a set of steps to resolve and validate the object references before execution. The search first looks at the identity of the connection executing the query. Let’s assume we have a user connection through "user1". For “select * from table1”, SQL Server 2000 searches for the object "user1.table1" first. If object does not exist, the next step is to search for "dbo.table1". These searches complete very quickly but can still be visible to the naked eye in cases where you have a workload with many ad-hoc queries (with today’s hardware this typically means over 100 queries per second) that contain not-fully-qualified object references. There is also a chance that, with the new flexibilities introduced in SQL Server 2005 around separation of users and schemas (http://msdn2.microsoft.com/en-us/library/ms190387.aspx), you may see a higher overhead in name resolution compared to SQL Server 2000. In extreme cases, we have seen the impact of name resolution to be 10-15% on throughput.

However SQL Server 2005 provides a mechanism to allow finer control over name resolution to the administrators. By manipulatng the value of the default_schema_name columns in the sys.database_principals Dynamic Management View, you can change the way name resolution is performed. You can assign a default schema for each database principal (a.k.a user) to go directly to the correct schema if an object name is not fully qualified, and bypass the unnecessary steps.

Reusing the example from above; with DEFAULT_SCHEMA set to 'dbo' in a SQL Server 2005 database, user1 executing 'select * from table1' will directly resolve immediately to 'dbo.table1' without searching for 'user1.table1'.

In fact, if you use the new CREATE USER syntax from SQL server 2005, you will get ‘dbo’ as the DEFAULT_SCHEMA by default. So if you are developing in a new database under SQL Server 2005, you are already taking advantage of this name resolution shortcut. However, the behavior for upgraded databases is different . During upgrade from previous versions of SQL Server, to preserve the same behavior, SQL Server 2005 sets the DEFAULT_SCHEMA to the name of the user. Example: ‘user1’ gets DEFAULT_SCHEMA of 'user1' when you upgrade a database from an earlier version to SQL Server 2005 by attaching or any other method. The reality is, in most instances, applications don't use anything but the ‘dbo’ schema. Nevertheless, it is close to impossible to detect whether your database and queries contain references to only objects in a specific schema.

Why? Well, it may be that some of your objects and queries get generated dynamically and only parsed right before execution. Obviously preserving the backward compatible behavior also means that you still pay for the added cost of the late bound name resolution.

This was exactly the reason why a few of our partners saw higher CPU cycles after the upgrade and experienced slower throughput in SQL Server 2005. In those cases, DEFAULT_SCHEMA setting got us back the throughput we were expecting from SQL Server 2005. The improvements have been as much as 15% in throughput.

Obviously the best practice still stands: You should fully qualify all object names and not worry about the name resolution cost at all. The reality is, there are still many imperfect applications out there and this setting help great for those cases.

How do you know if this is an issue for your workload?

Lets take a look at ways we can tell if this setting could benefit your workload.

The brute force method is to look at your app code or SQL Server 2005 Profiler for the queries that you are executing. If you do not have fully qualified names and you are executing them through a database principal (user) that has a DEFAULT_SCHEMA value that does not have the intended schema name for the not-fully-qualified objects in your queries, then you have the problem.

Reiterating the example from above; user1, with DEFAULT_SCHEMA='user1', executing 'select * from table1' and resolving to 'dbo.table1' could benefit from setting DEFAULT_SCHEMA to 'dbo'.

The other option is to use sys.dm_exec_cached_plans or sys.dm_exec_query_stats to look at the cached plans. Again you can go after the text and look for name qualification. You can also look for the results from the following query.

You can use the following query to see the cached plans that may indicate an issue with not-fully-qualified object names;
SELECT * FROM
(
SELECT e.[text], e.[user_id], e.[dbid]
FROM (
SELECT b.text,c.attribute,c.value
FROM sys.dm_exec_cached_plans AS a
OUTER APPLY sys.dm_exec_sql_text(a.plan_handle) AS b
OUTER APPLY sys.dm_exec_plan_attributes(a.plan_handle) AS c
) AS d
PIVOT (MAX(d.value) FOR d.attribute IN ("user_id", "dbid")) AS e
) AS f
WHERE [dbid] not in (32767) -- leave out the resource database
AND [user_id] not in (-2,1,4) -- leave out the generic plans, plans that belong to dbo and sys

If the resultset is non-empty, your application may be a good candidate for the DEFAULT_SCHEMA setting consideration. This is a good shortcut but is not a 100% guarantee. Be aware that sys.dm_exec_cached_plans contains only the cached plans. The cache fluctuates under memory pressure so some queries may get pushed out of the cache due to aging and pressure or, in some cases, may not get cached at all due to cost or security restrictions.

One additional thing to watch for; If you do not see a range of user_ids in the resultset, the benefits from setting the DEFAULT_SCHEMA may not be significant due to other optimizations and shortcuts in the engine around plan caching.

How do you change the DEFAULT_SCHEMA?
Assuming your database is using objects only in the dbo schema here is what you would run for each user:
ALTER USER WITH DEFAULT_SCHEMA=dbo

You can validate the current DEFAULT_SCHEMA settings by looking at the default_schema_name column in the sys.database_principals Dynamic Management View.
Cross Posted from http://blogs.microsoft.com/mssqlisv
Posted by mssqlisv 2 Comments
Thursday, February 22, 2007 11:47 AM
why cannot i GRANT EXECUTE permission on assemblies anymore ?
Just a quick note that this is by design and no you don't need to use it.
What are we talking about ?
Books online talks about granting assembly permissions. You used to do
GRANT execute on ASSEMBLY :: to with SQL Server 2005
Your database holds your assemblies and as such you could grant / revoke limited set of permissions. We initially allowed you to grant execute permission on the assembly but now when you execute the same on a server with the SP2 service pack, you get the following :
Msg 102, Level 15, State 1, Line 0
Incorrect syntax near 'EXECUTE...'.
Why did this change ?
Execute permission gave a false sense of security and was really inappropriate. Here's why:
As we shipped SQL Server 2005, this permission set was supported in TSQL but never enforced. So you could say "deny execute on assembly" but nothing happened. With this fix to revert the support from TSQL, you will not be misled anymore and we wanted to get this fix to you in a service pack of the same product that introduced you this immensely easy way of expressing your logic in assemblies. Going forward, with database upgrades to next release, any lingering assemblies that display this permission will be automatically corrected. And so you don't have to worry about a thing.
So how can i secure assemblies registered to the server ?
The assembly is really a container for types, objects etc which are independently permissible. The assembly itself can be marked safe, external_access and unsafe. In addition, you can use the 'trustworthy' property of the database for similar effect. For a complete list with description of our security model, please refer CLR Integration security in Books Online.
Thanks,
Mayank Nagar

Optimizing SQL Server Database Design

2009-01-14T01:43:00.000-08:00

When designing columns to store formatted data, such as Social Security numbers or phone numbers, you have two choices. You can choose to store the data with no formatting or with formatting. Each choice has its pros and cons. If you store the data with formatting, then your disk space requirements will be slightly higher than if you don't store the data with formatting. If you store the data without formatting, then you will have to add or remove formatting each time you INSERT or UPDATE a record, which adds to CPU and memory overhead on the server. In other words, the choice you make affects your server's load.
Which option you should choose in order to maximize performance is not always obvious, but many database designers choose to store formatted data because they feel the savings in CPU time and memory overhead (because formatting isn't performed on the fly) is more important than saving some space on the disk drive. You will have to examine your application closely to determine, in your particular case, which option will save your application the most overhead. [6.5, 7.0, 2000, 2005]

The TRUSTWORHY bit database property in SQL Server 2005

2009-01-13T02:34:00.000-08:00

In SQL Server 2005 we introduced a new database property named TRUSTWORTHY bit (TW bit for short) at the database level in order to work as a safeguard to reduce the default surface area regarding some powerful new features: EXECUTE AS USER and CLR assemblies. These new features are really powerful, but without the TW bit they would be ultimately under the control of the DBO and can potentially be misused to escalate privileges in the system.

When the TW bit is OFF
· EXECUTE AS USER:

· If the TW bit is OFF, EXECUTE AS USER will be bound to the DB and the server-scoped permissions of the impersonated user and roles will be used as deny-only. Access to other DBs will be limited to access as guest.

· CLR:

· Only assemblies marked as SAFE can be created and used.

But when the TW bit is ON, it doesn't blindly change the behavior of these features, instead it allows the system to verify permissions on the DBO (based on the SID) on the server or on another database to allow extended functionality.

When the TW bit is ON
· EXECUTE AS USER:

· DBO role: authenticator. The DBO will be part of the token as an authenticator; this means that the DBO will “vouch” for the impersonated token, and it is up to the server or another DB to trust this DBO.

· For cross-DB access, the DBO requires AUTHENTICATE permission. The impersonated context will be valid for the target DB, but still sandboxed from the server

· For access across the server, the DBO requires AUTHENTICATE SERVER permission. The impersonated context will be valid across the server (and all the DBs). *Read security Note*

· CLR :

· DBO role: establish the assembly types allowed in the DB

· For using assemblies marked as EXTERNAL_ACCESS in the DB, the DBO requires EXTERNAL_ACCESS permission. *Read security Note*

· For using assemblies marked as UNSAFE in the DB, the DBO requires UNSAFE permission. *Read security Note*

When the DBO is a low privileged principal in the system, and the DBO has good control on the DB (i.e. All the privileged principals in the DB are indeed trusted individuals), the TW bit works as expected and it can be used to establish a trusted environment with the features I just mentioned.

Security Note: The set of functionality marked with the *Read Security Note* text are indeed very powerful and can be easily misused if not taken into account. These features assume that the DBO (and privileged principals in the DB) are indeed fully trusted as they could in essence escalate their own privileges to sysadmin.

Limitations of the TW bit
The main limitation of this mechanism is the level of granularity: The whole DB is marked as TW or not, and it is not possible to distinguish between scenarios within the same DB. This presents a problem to the DBAs: either you trust the DBO (and everyone the DBO has entrusted with elevated permissions on the DB) or not, there is no middle ground.

Also notice the overloaded significance of the TW bit. It may be possible that you only want to use one of the features it is gating, for example, the creation of EXTERNAL ACCESS assemblies, but because of its boolean nature, it would also allow the rest of the features. At that point the permissions granted to the DBO are the ones that will truly enable/disable any of these features.

The main risk: TW bit ON & DBO is sysadmin
The risk with using the TW bit arises not from enabling it, but in conjunction to the nature of DBO. In practice, the first condition I mentioned (DBO is a low privileged user) is rarely true, and under these circumstances (DBO is a sysadmin & the TW bit enabled) all of the features I mentioned on the TW ON section are immediately enabled on the DB, even if the DBA only desired to use one of them.

If the desired behavior is indeed to enable all these features on the DB, and the privileged users on the DB are indeed trusted, there is no need to worry as the system will behave as expected, but it would be a good idea to keep such DBs closely monitored. Notice that in SQL Server 2005, out-of-the-box we already have a scenario with the TW bit ON & sysadmin: msdb. MSDB is considered a system DB with various system modules, and the assumption is that DBAs are already protecting msdb as they would protect other system DBs such as master DB.

Unfortunately the case above is rarely the desired behavior, and based cases we have observed, we detected two different kinds of mistakes when enabling the WT bit:

· Something breaks: The application works perfectly during development/testing, but once it is deployed on a tightly controlled environment the application breaks.

· Nothing breaks: The application works, but the attack surface area has been increased accidentally.

The solution for the first type of mistakes is simple: Grant the appropriate permissions to the DBO. It is the second type scenario the one that we should really worry about as it may not be obvious and it could allow a rogue user in the DB to escalate his privileges and take control of SQL Server.

Conclusions

The TW bit is used as a mechanism to control the surface area on very powerful features, but it relies on the DBO to be following least privilege principles, and not being a sysadmin. Having the combination of a sysadmin being the DBO on a DB that has been marked as TW bit = ON is a very dangerous one and we recommend to avoid it whenever it is possible.

As an alternative to the TW bit, in SQL Server 2005 we also introduced the notion of module signing, and the digital signatures can be used to extend the impersonation context beyond the DBand to establish an explicit trust on signed assemblies.

How to create a SQL trace using T-SQL

2009-01-13T02:31:00.000-08:00

Some users want to know if there is a way to monitor events on SQL server without using SQL Profiler. Yes, there is: the engine support behind SQL Profiler is the feature called SQL Trace which is introduced in SQL 2005. SQL Trace provides a set of stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within user's own applications to create traces manually, and allows to write custom applications specific to their needs.

The following sample code shows how to create customized SQL trace to monitor events to user's interest.

-- sys.traces shows the existing sql traces on the server

select * from sys.traces

go

--create a new trace, make sure the @tracefile must NOT exist on the disk yet

declare @tracefile nvarchar(500) set @tracefile=N'c:\temp\newtraceFile'

declare @trace_id int

declare @maxsize bigint

set @maxsize =1

exec sp_trace_create @trace_id output,2,@tracefile ,@maxsize

go

--- add the events of insterest to be traced, and add the result columns of interest

-- Note: look up in sys.traces to find the @trace_id, here assuming this is the first trace in the server, therefor @trace_id=1

declare @trace_id int

set @trace_id=1

declare @on bit

set @on=1

declare @current_num int

set @current_num =1

while(@current_num <65)

begin

--add events to be traced, id 14 is the login event, you add other events per your own requirements, the event id can be found @ BOL http://msdn.microsoft.com/en-us/library/ms186265.aspx

exec sp_trace_setevent @trace_id,14, @current_num,@on

set @current_num=@current_num+1

end

go

--turn on the trace: status=1

-- use sys.traces to find the @trace_id, here assuming this is the first trace in the server, so @trace_id=1

declare @trace_id int

set @trace_id=1

exec sp_trace_setstatus @trace_id,1

--pivot the traced event

select LoginName,DatabaseName,* from ::fn_trace_gettable(N'c:\temp\newtraceFile.trc',default)

go

-- stop trace. Please manually delete the trace file on the disk

-- use sys.traces to find the @trace_id, here assuming this is the first trace in the server, so @trace_id=1

declare @trace_id int

set @trace_id=1

exec sp_trace_setstatus @trace_id,0

exec sp_trace_setstatus @trace_id,2

go

The granularity level of SQL trace is server-wide event groups, which means it doesn’t allow to auditing specific event or action by specific user in a specific database. If you need a more granulated audit mechanism, you can start to look at the SQL Server Audit feature which is introduced in SQL Server 2008.

Have You Backed Up Your Database Master Key?

2009-01-13T01:19:00.000-08:00

If you have encrypted columns in a database or certificates used to create them, you need to make sure that you back up the Database Master Key as part of your maintenance, and then protect that backup file. Here's the short version of the command:

BACKUP MASTER KEY
TO FILE = ''
ENCRYPTION BY PASSWORD = '' ;
GO

http://msdn.microsoft.com/en-us/library/aa337546(SQL.90).aspx

How do exes/dlls generated with the /platform:x switch interact?

2009-01-13T00:58:00.000-08:00

Let's assume we have the following three dlls:

anycpu.dll -- compiled "any cpu"

x86.dll -- compiled "x86"

x64.dll -- compiled "x64"

And the following three exes:

anycpu.exe -- compiled "any cpu"

x86.exe -- compiled "x86"

x64.exe -- compiled "x64"

What happens if you try to use these exes and dlls together? We have to consider two possible scenarios, running on a 32-bit machine and running on a 64-bit machine...

On a 32-bit x86 machine:

anycpu.exe -- runs as a 32-bit process, can load anycpu.dll and x86.dll, will get BadImageFormatException if it tries to load x64.dll

x86.exe -- runs as a 32-bit process, can load anycpu.dll and x86.dll, will get BadImageFormatException if it tries to load x64.dll

x64.exe -- will get BadImageFormatException when it tries to run

On a 64-bit x64 machine:

anycpu.exe -- runs as a 64-bit process, can load anycpu.dll and x64.dll, will get BadImageFormatException if it tries to load x86.dll

x86.exe -- runs as a 32-bit process, can load anycpu.dll and x86.dll, will get BadImageFormatException if it tries to load x64.dll

x64.exe -- runs as a 64-bit process, can load anycpu.dll and x64.dll, will get BadImageFormatException if it tries to load x86.dll

Indexes for aggregates

2008-12-28T21:13:00.000-08:00

It’s well known that indexes on columns used in where clause and for joins is a good thing in SQL, but what about other places. How about on aggregates?

Consider a simple table with an amount and a customerID. It’s a common requirement to calculate the total amount that each customer has paid. No conditions are enforced, so this would seem like a place where an index won’t help. Well, let’s see. (sample code at end)

The clustered index (and hence the physical order of the rows) is on the identity column.Take the following query.
SELECT CustomerID, SUM(Amount) FROM Payments group by customerID

Without any additional indexes, SQL will execute that as a hash match (aggregate) which comprises 63% of the query’s cost. That is because the data is not ordered by the grouped column, and hence cannot be simply be summed.

Now add an index on CustomerID, Amount and run the same query. Now, the aggregate can be satisfied just with the non clustred index and, more importantly, the data is in order of customerID, so just a stream aggregate is required and the data does not have to be resorted or run through a hashing algorithm. Moreover, the stream aggregate is only 15% of the query’s cost

CREATE TABLE Payments (
PaymentKey INT IDENTITY PRIMARY KEY,
CustomerID int,
InvoiceID int,
PaymentDate datetime,
Amount Numeric(17,5)
)

;WITH DataPopulate (RowNo, CustomerID ,InvoiceID ,ADate, Amount) AS (
SELECT 1 AS RowNo, FLOOR(RAND()*40) as CustomerID, FLOOR(RAND()*200) as InvoiceID,
DATEADD(dd, FLOOR(RAND()*75454),'1753/01/01'), rand()*856542 AS Amount
UNION ALL
SELECT rowNo+1, FLOOR(RAND(RowNo*85455)*40) AS CustomerID,
FLOOR(RAND(RowNo*85455)*500) AS InvoiceID,
DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753/01/01'),
rand(RowNo*25411)*86542 AS Amount
FROM DataPopulate WHERE RowNo<10000
)
INSERT INTO Payments (CustomerId, InvoiceID, PaymentDate, Amount)
SELECT CustomerID ,InvoiceID ,ADate, Amount FROM DataPopulate
OPTION (MAXRECURSION 10000)

When did CheckDB last run?

2008-12-28T20:08:00.000-08:00

If corruption is found in a database it’s very important to know when the corruption started. In the cases where a restore is needed to fix the corruption, knowing which backup is clean can save a great deal of time in restoring databases and checking them.

On SQL 2000, the only way to know was to to go back through the history of the checkDB jobs (everyone is running checkDB regularly, right?) and see when the last successful run was.

On SQL 2005 and higher, when checkDB runs successfully, ie without finding any errors, it writes the date of the last run into the database’s boot page. Whenever SQL Server then starts that database up, be it during a server start, a database restore or bringing the database online, SQL prints a message into the error log.

CHECKDB for database ‘Testing’ finished without errors on 2008-12-22 10:20:06.007 (local time).

This makes it very easy to see when the database was last known to be clean (without any corruption). Note that SQLL is not running checkDB when it prints that. It’s simply printing out the date that CheckDB last completed without finding any problems. The information can also be read out of the boot page using an undocumented DBCC command.

1. Use Testing
2. GO
3.
4. DBCC TRACEON(3604)
5. DBCC DBINFO
6. DBCC TRACEOFF(3604)

Use Testing GO DBCC TRACEON(3604) DBCC DBINFO DBCC TRACEOFF(3604)

This prints out a whole bunch of information, all found in the database’s boot page. The info of interest here is in the second block (on SQL 2008 RTM)

dbi_dbccLastKnownGood = 2008-12-22 10:20:06.007

If the date listed is 1900-01-01 00:00:00, it means that CheckDB has never run successfully on that database. If that is the case, there will be no entries in the error log when the database is started.

Measuring Performance of Web Server

2008-12-26T01:53:00.000-08:00

Measuring Web server performance is a skill that can only be refined by repeated experience and experimentation. There are many variables at play, such as the number of clients, speed of client connections, server resources, application code, and so on. It helps to have good tools at your disposal, and fortunately those are available.

Microsoft provides the Web Application Stress (WAS) tool, which simulates multiple HTTP clients hitting your Web site. You can control the client load, number of connections, format of cookies, headers, and several other parameters from the tool's graphical interface. After a test run, WAS provides you with reports containing performance metrics such as response time, throughput, and performance counter data relevant to your application. The goal is simple: to maximize throughput and CPU utilization under high degrees of load. WAS is available from the Microsoft Internet Information Server Resource Kit and is also downloadable separately from http://webtool.rte.microsoft.com.

ASP.NET also exposes a number of performance counters that can be used to track the execution of your applications. Unlike traditional ASP, most of these performance counters are exposed per-application, instead of globally for the entire machine. The per-application counters are available under the ASP.NET Framework applications performance object, and you need to select a particular application instance when selecting a counter to monitor. Of course, you can still see the counter values for all applications using a special "__Total__" application instance in System Monitor. ASP.NET also exposes global-only counters which are not bound to a particular application instance. These counters are located under the ASP.NET System performance object. To view all available counters for ASP.NET (on Windows 2000 systems):

1. Select Start->Programs->Administrative Tools->Performance.
2. Click the View Report button in System Monitor.
3. Click the Add button.
4. Select ASP.NET Applications, then choose the All counters radio button. Click OK.
5. Select ASP.NET, then choose the All counters radio button. Click OK.

The ASP.NET Trace feature is also useful for identifying performance bottlenecks in your code. It can show you important timing information between successive trace output statements, as well as information about the server control heierarchy, the amount of viewstate used, and the render size of controls on your page.

Using xp_ReadErrorLog in SQL Server 2005

2008-12-25T20:30:00.000-08:00

I would like to share some interesting parameters I found for the undocumented extended stored procedure xp_ReadErrorLog. In doing some testing with this extended stored procedure I found four very interesting parameters. Adding to some of the articles already on the web that discuss undocumented stored procedures, in this article I will explain my testing, use and some examples of the procedure.

Parameters revealed
While working on some system startup procedures that would be making use of the xp_ReadErrorLog extended stored procedure, I came across some very interesting and useful parameters for the procedure. In testing I discovered some of the hidden parameter options that are similar, but still different in the way the extended stored procedure works from version SQL 2000 to SQL 2005. These are SQL 2005 only options. The parameter data types and size were determined by investigating the undocumented stored procedure sp_ReadErrorLog that uses the extended stored procedure.

Well, the interesting part starts now with the parameters. As in the previous versions parameter 1 reads the error log number passed to it, where the default "0" reads the current log.

xp_ReadErrorLog

LogDate ProcessInfo Text
2008-03-04 12:11:01.450 Server Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) Mar 23 2007 16:15:11
2008-03-04 12:11:01.500 Server (c) 2005 Microsoft Corporation.
2008-03-04 12:11:01.500 Server All rights reserved. 2008-03-04 12:11:01.500 Server Server process ID is 1284.
2008-03-04 12:11:01.500 Server Authentication mode is MIXED.
2008-03-04 12:11:01.510 Server Logging SQL Server messages in file 'D:\SRVAPPS\MSSQL.1\MSSQL\LOG\ERRORLOG'.

Now let's investigate Parameter (2). It turns out that a value of 1 (default) instructs the procedure to read the SQL error log. By passing a value of 2, the SQL server Agent log is read. Yes, the Agent log! So for example: xp_ReadErrorLog 0, 2 reads the current SQL server Agent log. Also note when using parameter 2 with the extended stored procedure that the column heading returned also changes.

xp_ReadErrorLog 0,2

LogDate ErrorLevel Text
2008-03-04 12:11:10.000 3 [393] Waiting for SQL Server to recover databases...
2008-03-04 12:11:14.000 3 [100] Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) ..
2008-03-04 12:11:14.000 3 [101] SQL Server xxxxxxxx version 9.00.3159 (0 connection limit)
2008-03-04 12:11:14.000 3 [102] SQL Server ODBC driver version 9.00.3042 2008-03-04 12:11:01.450 Server

Now we know that we can read both SQL logs (error and agent) for any log file, so now let's look at parameter (3). For those times when you need to find some value in the logs and have used the old trick/process of reading the extended stored procedure into a table and then searching through the table to find a value, we now have parameter (3). Parameter 3 is a search string that can be used to return just the log entry rows that contain the value in the search string. And to make it even better or to refine the search further, parameter 4 is also a search string.

An extra feature of this new version is that parameters 3 and 4 can be used in conjunction with each other for searching SQL error log (parameter 2 = 1) or SQL Agent log (parameter 2=2). So for example, xp_ReadErrorLog 0,1,'failed' will read the current SQL error log and return only rows that contain "failed". For an example of using parameter (4) example, xp_ReadErrorLog 0,1,'failed','login' will read current SQL error log returning only rows that contain "failed" and "login" in the same row. This makes it quite easy for retrieving those log entries for failed user logins from the SQL error logs, or maybe looking for those failed Agent jobs. Or those occasional times when you need to quickly find the port SQL started on or what machine the cluster is executing on.

xp_ReadErrorLog 0, 1, 'Failed', 'login'

LogDate ProcessInfo Text
2008-03-04 12:11:12.340 Logon Login failed for user 'Domain\xxxxxx'. [CLIENT: ]
2008-03-04 15:29:08.710 Logon Logon failed for login 'NT AUTHORITY\NETWORK SERVICE' due to trigger execution.
2008-03-04 15:29:08.710 spid54 The client was unable to reuse a session with SPID 54, which had been reset...

Parameters defined
* Parameter 1 (int), is the number of the log file you want to read, default is "0" for current log. The extended stored procedure xp_enumerrorlogs will come in handy in determining what SQL server error logs or SQL server Agent logs exist and when they were created. NOTE: extended stored procedure xp_enumerrorlogs parameter (1) works just like xp_ReadErrorLog parameter (2). A value of 1 shows available SQL error logs and a value of 2 shows Agent logs. The default value is 0.
* Parameter 2 (int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs, with a default value of 1.
* Parameter 3 varchar (255), is a search string for the log entry, with a default value of NULL.
* Parameter 4 varchar (255), is another search string for the log entry, with a default value of NULL.

Background
An extended stored procedure is a dynamic link library that runs inside SQL server. It can execute from Query Analyzer or SQL Server Management Studio (SSMS) for example. In most cases these extended stored procedures can only be executed by users with sysadmin privileges. Also note as Microsoft has always said about undocumented processes may change in future release.

Extract User-Role Mapping

2008-12-23T20:36:00.000-08:00

This script can be used with SQL 2000 and 2005. For example if you have 10 users in current database, it will retrieve all Group information along with default database, if the user has a login account else if will return a blank.

Begin
--Create a temp table that will hold the main result. This script will check your version, if SQL 2005 or 2000,
--because sp_helpuser return 6 values in SQL 2000 and 7 values in SQL 2005.
Create table #tmpUserPerm
(UserName varchar(100),
GroupName varchar(100),
LoginName varchar(100),
DefDBName varchar(100),
UserId int,
SID varbinary(100),
DefSchemaName varchar(100) null)

Declare @name varchar(100)
Declare @ver varchar(100)

--Create a temp table that will store all users except DBO and GUEST. If you want all users then
--you can remove "and name not in ('DBO', 'GUEST')" from the following statement.

select uid, name into #TmpUser from sysusers
where issqluser = 1 and hasdbaccess <> 0 and name not in ('DBO', 'GUEST')

--Execute the below query to get current version of SQL SERVER
set @ver = convert(varchar(100),SERVERPROPERTY('productversion'))

if (@ver = '9.00.3054.00') --If SQL 2005 then
begin
--Run a cursor for all users
declare cur Cursor for Select name from #Tmpuser
open cur
fetch next from cur into @name
while @@fetch_Status = 0
BEGIN
--Get data from sp_helpuser for current value of user (@NAME)
insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName, DefSchemaName, UserId, SID)
Exec sp_helpuser @name
fetch next from cur into @name
END
close cur
deallocate cur
drop table #Tmpuser
select * from #tmpUserPerm order by 1
drop table #tmpUserPerm
END
else --If SQL SERVER 2000 or other
begin
--Run cursor for all the user names
declare cur1 Cursor for Select name from #Tmpuser
open cur1
fetch next from cur1 into @name
while @@fetch_Status = 0
BEGIN
--Get data from sp_helpuser for current value of user (@NAME)
insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName, UserId, SID)
Exec sp_helpuser @name
fetch next from cur1 into @name
END
close cur1
deallocate cur1
drop table #Tmpuser
select username, groupname, loginname, defdbname from #tmpUserPerm order by 1
drop table #tmpUserPerm
end
end

Performance Tuning SQL Server Hardware

2008-12-23T00:48:00.000-08:00

Generally, it is better to run fewer SQL Server databases on more smaller servers than it is to run many databases on one larger server. Here are some reasons why:

* Purchasing several smaller SQL Servers may be less expensive than buying one huge SQL Server, although this may not seem logical to many people not familiar with server hardware. Generally, smaller servers can be purchased at commodity prices, while very large servers are special order and have a cost premium associated with them.

* In many cases, your current, older SQL Server may not be upgradeable, or if it is, it may not be cost-effective to upgrade it as compared to purchasing new physical servers.

* As physical servers get larger (more CPUs), there is more and more CPU overhead generated, which in effect reduces the overall performance of the server. Each additional CPU adds a decreasing amount of additional CPU power. Two 4-CPU servers are more efficient from a CPU perspective than a single 8-CPU server.

* Some databases need to be tuned differently than other databases. If all of your databases are located on a single server, then you can't take advantage of SQL Server-wide performance tuning techniques to tune each separate database as each database will have to share all SQL Server-wide performance tuning settings.

* If the single, large server goes down, then all the databases go down. If the databases are on separate servers, then fewer databases go down.

* Most SQL Server-based applications grow over time. If each database is located on its own server, then it is easier to add incremental hardware (such as RAM) to the servers that need it. On a large server, you may find that you can't expand beyond a certain point.

[6.5, 7.0, 2000, 2005]

Enabling vardecimal storage format

2008-12-21T20:30:00.000-08:00

First, this feature is only available in EE and Dev SKU.

Enabling vardecimal storage format on a table is a two step process as follows:

First you need to enable database for Vardecimal storage format. This can be done using the stored procedure sp_db_vardecimal_storage_format. The exact command is as follows

exec sp_db_vardecimal_storage_format '', 'ON'

When the above command is executed, SQL Server internally bumps the database version number but no tables are enabled for Vardecimal storage format. The database version needs to be bumped to indicate that the data in this database can potentially have a different storage format (i.e. the Vardecimal storage format). This is used to prevent attaching a Vardecimal enabled database to earlier versions of SQL Server 2005 as those versions don’t know how to interpret the new storage format. You can only enable Vardecimal storage format on user databases.

To find out which database(s) is enabled for Vardecimal storage format, you can use the following command

exec sp_db_vardecimal_storage_format

Once you have enabled the database for Vardecimal storage format, you can now choose to enable one or more tables (based on the potential disk savings using the tool described earlier) with this new storage format as follows

sp_tableoption '', 'vardecimal storage format', 1

This command, potentially an expensive one (same order as creating an index), converts all the rows in the table containing columns of type decimal/numeric to Vardecimal storage format. During this conversion, the table is locked and is not available. If the table has no clustered index, then all non-clustered indexes are rebuilt because the RIDs of the rows will change due to storage format change. However, if you have clustered index on the table, then only the non-clustered indexes containing decimal numeric column as key or included column need to be rebuilt. Note, that you cannot enable vardecimal storage format on all tables. Before enabling vardecimal storage format, SQL Server needs to make sure that we can always revert back to static storage format for decimal data and that update of decimal/numeric data always succeeds. If these conditions are not satisfied, the conversion to vardecimal storage format is denied.

To disable Vardecimal storage format on the table, you can use the following command

sp_tableoption '', 'vardecimal storage format', 0

SQL Server guarantees that you can always revert back to ‘static’ storage format unless you run out of disk space during conversion. Note, that the space overhead, to enable/disable Vardecimal storage format, is of the same order as building an index and it is not an online operation.

You can use the following command to find out which tables(s) has been enabled for Vardecimal storage format

select objectproperty(object_id('’), 'TableHasVarDecimalStorageFormat')

or

select name, object_id, type_desc
from sys.objects
where
objectproperty(object_id, N'TableHasVarDecimalStorageFormat') = 1

Reducing the Size of your Database in SQL Server 2005/SP2

2008-12-21T20:25:00.000-08:00

An exciting new feature in SQL Server 2005/SP2 is Vardecimal Storage Format. This storage format lets you reduce the size of your table significantly if the table has one of more columns of type decimal or numeric without requiring any changes to your application.

Up until now, the decimal and numeric types are stored as fixed length data in SQL Server. Both of these types are functionally equivalent and have a format of (p, s) where p is the precision (number of decimal digits) and s is the scale representing number of digits after the decimal. Depending on the precision (it can be declared in the range from 1 to 38), the decimal value can take anywhere from 5 bytes to 17 bytes. This can bloat the size of the table, especially when you have small decimal values for a column declared with high precision requirement. This issue is similar to char (17) vs. varchar(17). In this case, if most of your character data is 1 or 2 characters long but the max value is 17 characters long, you can reduce the size of the table by declaring the column to be of type varchar(17) instead of char(17).

The new vardecimal storage format stores the decimal/numeric values in a variable length storage format. It provides efficient storage of decimal/numeric data by eliminating the leading/trailing zeros and only storing the minimum required bytes. Using this format, you can get significant space savings (depending on your data distribution) in the space required to store decimal/numeric data. You can enable vardecimal storage format at a table level.

In our in-house testing, we have seen significant reduction in the size of the FACT table(s) that has large number of decimal columns. FACT tables are typically the largest table in a Data Warehouse. Here are some the numbers from our testing.

Best case reduction in the size of the table

57%

69%

51%

Estimating the space savings with vardecimal storage format

Before enabling Vardecimal storage format, you may want to know the potential reduction in the size of the table. Clearly, if the table has no decimal/numeric columns, there will be no savings. Note, that even if you have a table with decimal/numeric column types, there is no guarantee that you will be able to reduce the size of the table by enabling Vardecimal storage format. Again, this issue is similar to VARCHAR (17) vs. CHAR(17). If all the values in the column type has 17 characters, then average row length will be larger with VARCHAR(17) because it will be stored in the variable portion of the record structure. Recall, you need 2 bytes to store the offset of the variable length column. Also, if VARCAHR(17) is the only variable length column in the table, there is another overhead of 2 bytes to store number of variable length columns in the row. So in this case, the worst case, declaring column type as VARCAHR(17) may cost you 4 bytes more for each row than CHAR(17).

SQL Server 2005/SP2 provides you a tool, a stored procedure, to estimate the ‘reduction in row size’ with Vardecimal storage format. The following example illustrates the reduction in row size for two tables that have same scheme but different data, t_decimal being the best case and t_decimal2 being the worst case (where each decimal value has max 38 digits as allowed by the declared precision)

create table t_decimal (c1 int, c2 decimal(10,2), c3 decimal (38,2), c4 varchar(10))

go

create table t_decimal2 (c1 int, c2 decimal(10,2), c3 decimal (38,2), c4 varchar(10))

go

-- insert rows into these tables.

declare @i int

select @i = 0

while (@i < 1000)

begin

insert into t_decimal values (1, 0.0,0.0, 'hello')

insert into t_decimal2 values

(1,12345678.99,123456789012345678901234567890123499.99, 'hello')

set @i = @i + 1

end

-- Now let us find the potential space savings for each of these tables

-- This is the best case

exec sys.sp_estimated_rowsize_reduction_for_vardecimal 't_decimal'

Here is the output. Note, in this case, you can reduce the size of the row by almost 50%. Also, if you have more decimal/numeric columns, the savings will be proportionally larger.

avg_rowlen_fixed_format avg_rowlen_vardecimal_format row_count

--------------------------------------- --------------------------

46.00 24.00 1000

-- this is worst case. Note in this case, the average row length actually increases

-- with Vardecimal storage format.

--

exec sys.sp_estimated_rowsize_reduction_for_vardecimal 't_decimal2'

avg_rowlen_fixed_format avg_rowlen_vardecimal_format row_count

------------------------- ---------- ---------------- ------
46.00 48.00 1000

SQL Server Storage Engine -Enabling compression on a HEAP

2008-12-21T20:21:00.000-08:00

I often get a question how to do enable compression on a table that is a heap (i.e. it does not have a clustered index). Clearly, one could create the clustered index with compression option set to PAGE and then drop the clustered index. This is an expensive operation because

· Creating a clustered index requires a SORT

· When you drop the clustered index, internal allocation structure, namely the PFS (Page Free Space) needs to updated to reflect the free space on each page. Note, when a clustered index is dropped, the resultant heap retains the compression state.

Instead, in SQL Server 2008, there is a new REBUILD command available that can be used to rebuild a HEAP. You can use this command to enable compression on the HEAP. Also, you can use this command to defragment a HEAP. You may wonder what is there to defrag in a heap because the pages don’t need to be traversed in the key order? Well, it is possible the pages in a HEAP are not full so the defragmentation can be used to reclaim the free space. In any case, let us get back to compression. Let me start with the following example

create table t1_big (c1 int, c2 int, c3 char(1000))

go

- Load the data

declare @i int

select @i = 0

while (@i < 5000)

begin

insert into t1_big values (@i, @i + 5000,

replicate('a', 500))

set @i = @i + 1

end

- Look at the current row size

- The following query returns a length of 1015

select max_record_size_in_bytes

from sys.dm_db_index_physical_stats (db_id('compression'),

object_id('compression.t1_big'),

null, null, 'DETAILED')

where object_name (object_id) like '%t1_big%'

-- Now, you can use the following NEW command to enable

-- PAGE compression. You could have used this command to

-- enable ROW compression or even NONE compression. As

-- part of rebuilidng the HEAP, it is also defragmented.

-- This command can also be executed ONLINE

alter table t1_big rebuild

with (

data_compression = PAGE)

-- compression setting shows the PAGE compression

select object_name (object_id) as table_name, data_compression_desc

from sys.partitions

where object_name (object_id) like '%t1_big%'

-- check the length again and it returns a value 9

-- Note, the example I have used here is not realistic so it

-- show almost 99% compression but you get the idea.

select max_record_size_in_bytes

from sys.dm_db_index_physical_stats (db_id('compression'),

object_id('compression.t1_big'),

null, null, 'DETAILED')

where object_name (object_id) like '%t1_big%'

There are few restrictions on ALTER TABLE REBUILD command as follows

(1) If you use ONLINE option then HEAP rebuild is single threaded. Note, this restriction is not there when you are rebuilding an index which supports multi-threaded ONLINE build. However, in both cases, OFFLINE build does support multi-threading.

(2) When you rebuild a HEAP and specify compression option, the corresponding non clustered indexes are also rebuilt because RIDs will change but the compression state of the indexes will not change. If you want to alter compression option of existing nonclustered indexes, you will need to execute ‘Alter Index’ command on each such indexes. So effectively, you will need to rebuild the nonclustered index twice if you want to change the compression setting both for the HEAP and its associated nonclustered indexes. Here is the rationale for this

a. Most index rebuild options are not applicable to HEAP. For this reason, we did not add ‘ALL’ keyword like the one with Alter Index command.

b. Since indexes are typically much smaller compare to the table, we think most customers will not want to change compression state on indexes automatically as part of rebuilding the HEAP or clustered index for that matter.

How to track database growth across multiple SQL Server instances

2008-12-21T20:02:00.000-08:00

It is easy to track database growth on a single SQL Server instance. We simply just need to store the results of sp_databases or loop through the databases and call sp_spaceused for each database.

If you support hundreds of SQL instances like I do, you'd want to store the database growth information in a central repository. From this central server, you could create a linked server for each SQL Server instance to track, but I hate creating linked servers. I especially hate having to create hundreds of them on one SQL Server instance. Instead of using linked servers, I created a CLR stored procedure. It requires one table.

You can download the code here. It includes the C# source code as well as the dll for the CLR object and a sample SQL script file to get it setup on your central server.

Once you have set it up, you can create a SQL job to call it. If you have a small number of SQL instances to administer, you can simply add multiple calls to isp_DatabaseGrowth, like this:

EXEC dbo.isp_DatabaseGrowth 'Server1\Instance1'

EXEC dbo.isp_DatabaseGrowth 'Server2'

If you have a large number of SQL instances to administer, I recommend looping through a table that contains one row for every SQL instance. Here is what my job step looks like:

DECLARE @serverId int, @serverName sysname, @max int

SET @serverId = 1

SELECT IDENTITY(int, 1, 1) AS ServerId, ServerName
INTO #Server
FROM dbo.Server
WHERE ServerName NOT IN ('Server1\Instance2', 'Server1\Instance3', 'Server3') --exclude certain SQL instances

SELECT @max = MAX(ServerId)
FROM #Server

WHILE @serverId <= @max
BEGIN
SELECT @serverId = ServerId, @serverName = ServerName
FROM #Server
WHERE ServerId = @serverId

EXEC dbo.isp_DatabaseGrowth @serverName

SET @serverId = @serverId + 1
END

DROP TABLE #Server

Here's the DDL for the Server table:

CREATE TABLE [dbo].[Server]
(
[ServerName] [sysname] NOT NULL,
CONSTRAINT [PK_Server] PRIMARY KEY CLUSTERED
(
[ServerName] ASC
)
)

If any of your databases were upgraded to SQL Server 2005, the data returned from sp_spaceused/sp_databases may contain incorrect data due to row count inaccuracies in the catalog views. Make sure to run DBCC UPDATEUSAGE on your databases after an upgrade to SQL Server 2005. Databases that were created in SQL Server 2005 do not have this issue.

Link Servers

2008-12-21T19:58:00.000-08:00

When you create a link between two SQL Servers, SQL Server does its best to perform as much work as it can on the remote server. This way, less data has to be moved from the remote server to the local server, helping to reduce overhead and boosting performance. But for some particular operations, they have to be performed on the local server, not the remote server. Some examples of locally performed operations include:

* Data conversion operations
* Queries that use the bit, timestamp, or uniqueidentifier data types
* Queries that use the TOP clause
* INSERTS, UPDATES, or DELETES

Because of this, you may want to try to avoid performing any of the above operations using a remote linked server.

If you are running a remote query against a linked server, and you want to find out which parts are performing on the remote server and which are performing on the local server, run the query from Query Analyzer or Management Studio and take a look at the query plan. It will tell you what part of your query is running where. It should be your goal to create code that runs mostly on the remote server, not the local server. [7.0, 2000, 2005]

Optimizing SQL Server Non-Clustered Indexes

2008-12-18T19:53:00.000-08:00

In some cases, even though a column (or columns of a composite index) has a non-clustered index, the Query Optimizer may not use it (even though it should), instead performing a table scan (if the table is a heap) or a clustered index scan (if there is a clustered index). This, of course, can produce unwanted performance problems.

This particular problem can occur when there is a data correlation between the order of the rows in the table, and the order of the non-clustered index entries. This can occur when there is correlation between the clustered index and the non-clustered index. For example, the clustered index may be created on a date column, and the non-clustered index might be created on an invoice number column. If this is the case, then there is a correlation (or direct relationship) between the increasing dates and the increasing invoice numbers found in each row.

The reason this problem occurs is because the Query Optimizer assumes there is no correlation, and it makes its optimization decisions based on this assumption.

If you run into this problem, there are three potential resolutions to this problem:

* If possible, reorder the non-clustered index column (assuming a composite index) so that the column with the highest cardinality is the first column in the composite index.
* Create covering indexes.
* Add index hints to your queries to overrule the Query Optimizer.

Tips for building a Work Breakdown Structure

2008-12-18T03:09:00.000-08:00

A Work Breakdown Structure (WBS) is the best way to understand the detailed work of the project when you have to build a schedule from scratch. It’s used to break the project down into the major phases, deliverables, and work components that will be built by the project. These work components can then be broken down into the activities that are required to build them. The WBS is not the same as the final schedule (which requires sequencing, resources, estimated effort, estimated duration, etc.).

Here are five tips to keep in mind when building your WBS:

#1: Create a WBS dictionary for large projects
Normally you wouldn’t need a WBS dictionary, but if your WBS has hundreds (or thousands) of detailed activities, there may just be too much to keep track of by hand. In this case, it might make sense to place all of the important information in a WBS dictionary. The dictionary helps keep track of all of the summary and detailed activities, including a short description, the WBS numeric identifier (1.1, 1.1.1, 1.1.2, etc.) and the estimated effort. If you enter your WBS dictionary into a specialized tool, the tool can also help to keep track of changes to the WBS as well.

#2: Use the summary activities as milestones
Your WBS should contain both detailed and summary activities. (A summary activity is one that is broken down further; a detailed activity is one that is not broken down further.) Although a schedule usually includes only detailed activities, it makes sense to include the summary activities as milestones (i.e., markers signifying that a deliverable or set of deliverables is complete). A summary activity can be used as a milestone since it would indicate that all of the underlying detailed work has been completed.

#3: Break activities into two or more detailed activities

I’ve seen teams that break one activity in the WBS into only one activity at the next level. In my opinion, this doesn’t make sense because then the detailed activity represents the same work as the prior summary activity. This doesn’t buy you anything.

#4: Make the final detailed activities action oriented
The detailed activities on your WBS (the ones that are not broken down further) are ultimately moved to your schedule. For that reason, it’s easier if the detailed activities in your WBS are action oriented — just as activities in your schedule would be. For example, instead of describing a detailed WBS activity as “meeting,” you should state it as “schedule a weekly meeting.” Instead of having a WBS detailed activity for “Testing Plan,” you should state it instead as “Create Testing Plan.” In this way, the detailed activities can be moved to the schedule with a minimum of wording changes.

#5: Don’t place requirements on the WBS
If you place a deliverable on your WBS, you can break this deliverable down into the activities that are required to create it. You don’t break a deliverable down into the requirements that describe it. Requirements do not belong on a WBS. Only deliverables and activities belong on the WBS.

By using these five techniques, you will save you time and rework on your next WBS.

The industry’s 10 best IT certifications

2008-12-17T02:44:00.000-08:00

IT certifications boast numerous benefits. They bolster resumes, encourage higher salaries, and assist in job retention. But which IT certifications are best?

Technology professionals generate much debate over just that question. Many claim vendor-specific programs best measure a candidate’s skills, while others propose vendor-independent exams are the only worthy way of measuring real-world expertise. Still other observers believe the highest-level accreditations — Microsoft’s MCSE or new Architect Series certification, Cisco’s CCIE, etc. — are the only credentials that truly hold value.

Myself, I don’t fully subscribe to any of those mindsets. The best IT certification for you, after all, is likely to be different from that for another technology professional with different education, skills, and goals working at a different company in a different industry. For that reason, when pursuing any professional accreditation, you should give much thought and care to your education, experience, skills, goals, and desired career path.

Once a career road map is in place, selecting a potential certification path becomes much easier. And that’s where this list of the industry’s 10 best IT certifications comes into play. While this list may not include the 10 best accreditations for you, it does catalog 10 IT certifications that possess significant value for a wide range of technology professionals.

#1: MCITP
The new-generation Microsoft Certified IT Professional credential, or MCITP for short, is likely to become the next big Microsoft certification. Available for a variety of fields of expertise — including database developer, database administrator, enterprise messaging administrator, and server administrator — an MCITP validates a professional’s proven job-role capabilities. Candidates must pass several Microsoft exams that track directly to their job role before earning the new designation.

As with Microsoft’s other new-generation accreditations, the MCITP certification will retire when Microsoft suspends mainstream support for the platforms targeted within the MCITP exams. By matching the new certification to popular job roles, as has been done to some extent with CompTIA’s Server+ (server administrator), Project+ (project manager), and A+ (desktop support) certifications, Microsoft has created a new certification that’s certain to prove timely, relevant, and valuable.

#2: MCTS
The new-generation Microsoft Certified Technology Specialist (MCTS) helps IT staff validate skills in installing, maintaining, and troubleshooting a specific Microsoft technology. The MCTS certifications are designed to communicate the skills and expertise a holder possesses on a specific platform.

For example, candidates won’t earn an MCTS on SQL Server 2008. Instead, they’ll earn an MCTS covering SQL Server business intelligence (MCTS: SQL Server 2008 Business Intelligence), database creation (MCTS: SQL Server 2008, Database Development), or SQL server administration (MCTS: SQL Server 2008, Implementation and Maintenance).

These new certifications require passing multiple, tightly targeted exams that focus on specific responsibilities on specific platforms. MCTS designations will expire when Microsoft suspends mainstream support for the corresponding platform. These changes, as with other new-generation Microsoft certifications, add value to the accreditation.

#3: Security+
Security continues to be a critical topic. That’s not going to change. In fact, its importance is only going to grow. One of the quickest ways to lose shareholder value, client confidence, and sales is to suffer a data breach. And no self-respecting technology professional wants to be responsible for such a breach.

CompTIA’s Security+ accreditation provides a respected, vendor-neutral foundation for industry staff (with at least two years of experience) seeking to demonstrate proficiency with security fundamentals. While the Security+ accreditation consists of just a single exam, it could be argued that any IT employee charged with managing client data or other sensitive information should, at a minimum, possess this accreditation. The importance of ensuring staff are properly educated as to systems security, network infrastructure, access control, auditing, and organizational security principles is simply too important to take for granted.

#4: MCPD
There’s more to information technology than just administration, support, and networking. Someone must create and maintain the applications and programs that power organizations. That’s where the new-generation Microsoft Certified Professional Developer (MCPD) credential comes into play.

The MCPD accreditation measures a developer’s ability to build and maintain software solutions using Visual Studio 2008 and Microsoft .NET Framework 3.5. Split into three certification paths (Windows Developer 3.5, ASP.NET Developer 3.5, and Enterprise Applications Developer 3.5), the credential targets IT professionals tasked with designing, optimizing, and operating those Microsoft technologies to fulfill business needs.

A redesigned certification aimed at better-measuring real-world skills and expertise, the MCPD will prove important for developers and programmers. Besides requiring candidates to pass several exams, the MCPD certification will retire when Microsoft suspends mainstream support for the corresponding platform. The change is designed to ensure the MCPD certification remains relevant, which is certain to further increase its value.

#5: CCNA
The Cisco Certified Internetwork Expert (CCIE) accreditation captures most of the networking company’s certification glory. But the Cisco Certified Network Associate (CCNA) might prove more realistic within many organizations.

In a world in which Microsoft and Linux administrators are also often expected to be networking experts, many companies don’t have the budgets necessary to train (or employ) a CCIE. But even small and midsize corporations can benefit from having their technology professionals earn basic proficiency administering Cisco equipment, as demonstrated by earning a CCNA accreditation.

As smaller companies become increasingly dependent upon remote access technologies, basic Cisco systems skills are bound to become more important. Although many smaller organizations will never have the complexity or workload necessary to keep a CCIE busy, Cisco’s CCNA is a strong accreditation for technology professionals with a few years’ experience seeking to grow and improve their networking skills.

#6: A+
Technology professionals with solid hardware and support skills are becoming tougher to find. There’s not much glory in digging elbow-deep into a desktop box or troubleshooting Windows boot errors. But those skills are essential to keeping companies running.

Adding CompTIA’s A+ certification to a resume tells hiring managers and department heads that you have proven support expertise. Whether an organization requires desktop installation, problem diagnosis, preventive maintenance, or computer or network error troubleshooting, many organizations have found A+-certified technicians to be more productive than their noncertified counterparts.

Changes to the A+ certification, which requires passing multiple exams, are aimed at keeping the popular credential relevant. Basic prerequisite requirements are now followed by testing that covers specific fields of expertise (such as IT, remote support, or depot technician). The accreditation is aimed at those working in desktop support, on help desks, and in the field, and while many of these staffers are new to the industry, the importance of an A+ certification should not be overlooked.

#7: PMP
Some accreditations gain value by targeting specific skills and expertise. The Project Management Professional (PMP) certification is a great example.

The Project Management Institute (PMI), a nonprofit organization that serves as a leading membership association for project management practitioners, maintains the PMP exam. The certification measures a candidate’s project management expertise by validating skills and knowledge required to plan, execute, budget, and lead a technology project. Eligible candidates must have five years of project management experience or three years of project management experience and 35 hours of related education.

As organizations battle tough economic conditions, having proven project scheduling, budgeting, and management skills will only grow in importance. The PMI’s PMP credential is a perfect conduit for demonstrating that expertise on a resume.

#8: MCSE/MCSA
Even years after their introduction, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA) credentials remain valuable. But it’s important to avoid interpreting these accreditations as meaning the holders are all-knowing gurus, as that’s usually untrue.

In my mind, the MCSE and MCSA hold value because they demonstrate the holder’s capacity to complete a long and comprehensive education, training, and certification program requiring intensive study. Further, these certifications validate a wide range of relevant expertise (from client and server administration to security issues) on specific, widely used platforms.

Also important is the fact that these certifications tend to indicate holders have been working within the technology field for a long time. There’s no substitute for actual hands-on experience. Many MCSEs and MCSAs hold their certifications on Windows 2000 or Windows Server 2003 platforms, meaning they’ve been working within the industry for many years. While these certifications will be replaced by Microsoft’s new-generation credentials, they remain an important measure of foundational skills on Windows platforms.

#9: CISSP
As mentioned with the Security+ accreditation earlier, security is only going to grow in importance. Whatever an organization’s mission, product, or service, security is paramount.

(ISC)², which administers the Certified Information Systems Security Professional (CISSP) accreditation, has done well building a respected, vendor-neutral security certification. Designed for industry pros with at least five years of full-time experience, and accredited by the American National Standards Institute (ANSI), the CISSP is internationally recognized for validating a candidate’s expertise with operations and network and physical security, as well as their ability to manage risk and understand legal compliance responsibilities and other security-related elements.

#10: Linux+
While pursuing my first Microsoft certification 10 years ago, I remember debating the importance of Linux with several telecommunications technicians. They mocked the investment I was making in learning Microsoft technologies. These techs were confident Linux was going to displace Windows.

Well, didn’t happen. Linux continues to make inroads, though. The open source alternative is an important platform. Those professionals who have Linux expertise and want to formalize that skill set will do well adding CompTIA’s Linux+ certification to their resumes.

The vendor-neutral exam, which validates basic Linux client and server skills, is designed for professionals with at least six to 12 months of hands-on Linux experience. In addition to being vendor-neutral, the exam is also distribution neutral (meaning the skills it covers work well whether a candidate is administering Red Hat, SUSE, or Ubuntu systems).

10 services to turn off in MS Windows XP

2008-12-16T02:01:00.000-08:00

As long as Microsoft Windows has been a network capable operating system, it has come with quite a few services turned on by default, and it is a good idea for the security conscious user of Microsoft’s flagship product to shut down any of these that he or she isn’t using.

Each version of MS Windows provides different services, of course, so any list of services to disable for security purposes will be at least somewhat particular to a given version of Microsoft Windows. As such, a list like this one needs to be identified with a specific Microsoft Windows version, though it can still serve as a guide for the knowledgeable MS Windows user to check out the running services on other versions as well.

If you are running Microsoft Windows XP on your desktop system, consider turning off the following services. You may be surprised by what is running without your knowledge.

* IIS – Microsoft’s Internet Information Services provide the capabilities of a Webserver for your computer.

* NetMeeting Remote Desktop Sharing — NetMeeting is primarily a VoIP and videoconferencing client for Microsoft Windows, but this service in particular is necessary to remote desktop access.

* Remote Desktop Help Session Manager – This service is used by the Remote Assistance feature that you can use to allow others remote access to the system to help you troubleshoot problems.

* Remote Registry – The capabilities provided by the Remote Registry service are frightening to consider from a security perspective. They allow remote users (in theory, only under controlled
circumstances) to edit the Windows Registry.

* Routing and Remote Access – This service bundles a number of capabilities together, capabilities that most system administrators would probably agree should be provided separately. It is rare that any of them should be necessary for a typical desktop system such as Microsoft Windows XP, however, so they can all conveniently be turned off as a single service. Routing and Remote Access provides the ability to use the system as a router and NAT device, as a dialup access gateway, and a VPN server.

* Simple File Sharing – When a computer is not a part of a Microsoft Windows Domain, it is assumed by the default settings that any and all filesystem shares are meant to be universally accessible. In the real world, however, we should only want to provide shares to very specific, authorized users. As such, Simple File Sharing, which only provides blanket access to shares without exceptions, is not what we want to use for sharing filesystem resources. It is active by default on both MS Windows XP Professional and MS Windows XP Home editions. Unfortunately, this cannot be disabled on MS Windows XP Home. On MS Windows XP Professional, however, you can disable it by opening My Computer -> Tools -> Folder Options, clicking the View tab, and unchecking the Use simple file sharing (Recommended) checkbox in the Advanced settings: pane.

* SSDP Discovery Service – This service is used to discover UPnP devices on your network, and is required for the Universal Plug and Play Device Host service (see below) to operate.

* Telnet – The Telnet service is a very old mechanism for providing remote access to a computer, most commonly known from its use in the bad ol’ days of security for remote command shell access on Unix servers. These days, using Telnet to remotely manage a Unix system may be grounds for firing, where an encrypted protocol such as SSH should be used instead.

* Universal Plug and Play Device Host – Once you have your “Plug and Play” devices installed on your system, it is often the case that you will not need this service again.

* Windows Messenger Service – Listed in the Services window under the name Messenger, the Windows Messenger Service provides “net send” and “Alerter” functionality. It is unrelated to the Windows Messenger instant messaging client, and is not necessary to use the Windows Messenger IM network.

On your system, these services may not all be turned on, or even installed. Whether a given service is installed and running may depend on whether you installed the system yourself, whether you are using XP Home or XP Professional, and from which vendor you got your computer if MS Windows XP was installed by a vendor.

With the exception of Simple File Sharing, all of the above listed services can be disabled from the same place. Simply click on the Start button, then navigate to Settings -> Control Panel, open Administrative Tools, and from there open the Services window. To disable any service in the list, double-click on its entry in that window and change the Startup type: setting. In general, you should change services you are turning off for security purposes to a “Disabled” state. When in doubt about whether a given service is necessary for other services, check the Dependencies tab in the service’s settings dialog.

Obviously, this is not a comprehensive list of everything running on your computer that you may want to turn off. It is merely a list of ten items that you most likely do not need to have running, and constitute a security vulnerability if left running. Most users will never have need of any of the services in this list, once the computer is up and running. Other services may be disabled without ill effect as well, though you should research each item in the complete services list before you disable it to ensure that you actually do not need it running. Some of them are quite critical to the normal operation of your system, such as the Remote Procedure Call (RPC) service.

Every running — but unused — service on your machine is an unnecessary security vulnerability. If a service is not important at all for authorized users and basic system functionality, turn it off.

Performance Tuning for Views

2008-12-15T20:19:00.001-08:00

While views are often convenient to use, especially for restricting users from seeing data they should not see, they aren't always good for performance. So if database performance is your goal, avoid using views (SQL Server 2000/2005 Indexed Views are another story).

Views can slow down queries for several different reasons. For example, let's look at these two SELECT statements:

* SELECT * FROM table_name
* SELECT * FROM view_name

Which is faster? If you test it, you will find that the first SELECT statement is faster, although the execution plan for both of them will be the same. How can that be? This is because it takes SQL Server extra work (such as looking up data in the system tables) before it can execute the view. This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.

Another way views can hurt performance is when JOINs or UNIONs are used, and you don't intend to use all of the columns. This results in SQL Server performing unnecessary work (such as an unnecessary JOIN or UNION), slowing down the performance.

Views, like stored procedures, once they are run the first time, are optimized and their execution plan is stored in cache in case they need to be reused. But this is not reason enough to use a view.

Views, besides hurting performance, are not all that flexible when you are working with them. For example, they can't be changed on the fly, they can’t be used to sort data, and using them for INSERTs, UPDATEs, and DELETEs is problematic. In addition, while views can be nested, this just compounds their problems, so avoid doing this.

Instead of using views, use stored procedures instead. They are much more flexible and they offer better performance. [7.0, 2000, 2005]

Microsoft Internet Explorer Multiple Vulnerabilities

2008-12-15T03:46:00.000-08:00

Some vulnerabilities have been reported in Microsoft Internet Explorer, which can be exploited by malicious people to compromise a user's system.

1) An error when handling parameters passed to unspecified navigation methods can be exploited to corrupt memory via a specially crafted web page.

2) An error when fetching a file with an overly long path from a WebDAV share can be exploited to corrupt heap memory via a specially crafted web page.

3) An unspecified use-after-free error can be exploited to corrupt memory via a specially crafted web page.

4) A boundary error when processing an overly long filename extension specified inside an "EMBED" tag can be exploited to cause a stack-based buffer overflow.

Successful exploitation of the vulnerabilities may allow execution of arbitrary code.

Patches are available for these critical issues.

Microsoft Excel Multiple Vulnerabilities

2008-12-15T03:44:00.000-08:00

Some vulnerabilities have been reported in Microsoft Excel, which can be exploited by malicious people to compromise a user's system.

1) An error while validating an index value in a NAME record can be exploited to corrupt memory via a specially crafted Excel Spreadsheet (XLS) file.

2) An unspecified error in the processing of Excel records can be exploited to corrupt memory via a specially crafted XLS file.

3) An unspecified error in the processing of Excel formulas can be exploited to corrupt memory via a specially crafted XLS file.

Successful exploitation of the vulnerabilities may allow execution of arbitrary code.

Patches are available for these critical issues.

Microsoft Office Word Multiple Vulnerabilities

2008-12-15T03:41:00.000-08:00

Multiple vulnerabilities have been reported in Microsoft Office Word, which can be exploited by malicious people to compromise a user's system.

1) An error when processing the "lcbPlcfBkfSdt" field within the FIB (File Information Block) can be exploited to corrupt memory via a specially crafted Word file.

2) An integer overflow error exists when calculating the space required for the specified number of points in a polyline or polygon. This can be exploited to cause a heap-based buffer overflow during parsing of objects in Rich Text Format (.rtf) files e.g. when a user opens a specially crafted .rtf file with Word or previews a specially crafted e-mail.

3) An unspecified error when parsing certain records can be exploited to corrupt memory via a specially crafted Word file.

4) An error exists when processing consecutive "\do" drawing object tags encountered in RTF documents. This can be exploited to free a heap buffer twice and corrupt memory.

5) An error when processing mismatched "\dpgroup" and "\dpendgroup" controlwords can be exploited to cause a buffer overflow via an RTF document containing an overly large number of "\dpendgroup" tags.

6) A boundary error when parsing RTF documents containing multiple drawing object tags can be exploited to cause a heap-based buffer overflow.

7) A boundary error when processing RTF documents can be exploited to overflow a static buffer via a document containing an overly large number of "\stylesheet" control words.

8) An error when processing a malformed table property can be exploited to cause a stack-based buffer overflow via a specially crafted Word document.

Successful exploitation of the vulnerabilities may allow execution of arbitrary code.
Patches are available for these critical issues.

2008 Index Fragmentation Maintenance

2008-12-09T19:46:00.001-08:00

Nothing to it. Run it and it hits every database on the system. You can put it inside a stored procedure or inside a SQL Agent Job.

DECLARE @DBName NVARCHAR(255)
,@TableName NVARCHAR(255)
,@SchemaName NVARCHAR(255)
,@IndexName NVARCHAR(255)
,@PctFrag DECIMAL

DECLARE @Defrag NVARCHAR(MAX)

CREATE TABLE #Frag
(DBName NVARCHAR(255)
,TableName NVARCHAR(255)
,SchemaName NVARCHAR(255)
,IndexName NVARCHAR(255)
,AvgFragment DECIMAL)

EXEC sp_msforeachdb 'INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment
) SELECT ''?'' AS DBName
,t.Name AS TableName
,sc.Name AS SchemaName
,i.name AS IndexName
,s.avg_fragmentation_in_percent
--,s.*
FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL,
NULL, ''Sampled'') AS s
JOIN ?.sys.indexes i
ON s.Object_Id = i.Object_id
AND s.Index_id = i.Index_id
JOIN ?.sys.tables t
ON i.Object_id = t.Object_Id
JOIN ?.sys.schemas sc
ON t.schema_id = sc.SCHEMA_ID
WHERE s.avg_fragmentation_in_percent > 20
AND t.TYPE = ''U''
ORDER BY TableName,IndexName'

DECLARE cList CURSOR
FOR SELECT * FROM #Frag

OPEN cList
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctFrag BETWEEN 20.0 AND 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE'
EXEC sp_executesql @Defrag
END
ELSE IF @PctFrag > 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' REBUILD'
EXEC sp_executesql @Defrag
END

FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag

END
CLOSE cList
DEALLOCATE cList

DROP TABLE #Frag

Performance Tuning for Stored Procedures

2008-12-09T19:38:00.000-08:00

For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner.object_name or schema_owner.object_name.

If the object owner's or schemas are not specified for objects, then SQL Server must perform name resolution on the objects, which causes a small performance hit.

And if objects referred to in the stored procedure have different owners or schemas, SQL Server must check object permissions before it can access any object in the database, which adds unnecessary overhead. Ideally, the owner or schema of the stored procedure should own all of the objects referred to in the stored procedure.

In addition, SQL Server cannot reuse a stored procedure "in-memory plan" over if the object owner or schema is not used consistently. If a stored procedure is sometime referred to with its object owner's or schema name, and sometimes it is not, then SQL Server must re-execute the stored procedure, which also hinders performance. [7.0, 2000, 2005]

How to change all Object Owners to dbo

2008-12-09T02:50:00.000-08:00

CreateALTER proc [dbo].[ChangeAllObjectOwnersTodbo]
as
set nocount on

declare @uid int
declare @objName varchar(50)
declare @userName varchar(50)
declare @currObjName varchar(50)
declare @outStr varchar(256)
set @uid = user_id('dbo')

declare chObjOwnerCur cursor static
for
select user_name(uid) as 'username', [name] as 'name' from sysobjects where uid <> @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
print 'All objects are already owned by dbo!'
close chObjOwnerCur
deallocate chObjOwnerCur
return 1
end

fetch next from chObjOwnerCur into @userName, @objName
while @@fetch_status = 0
begin
set @currObjName = 'dbo.' + @objName
if (object_id(@currObjName) > 0)
print 'WARNING *** ' + @currObjName + ' already exists ***'
set @outStr = 'sp_changeobjectowner ''' + @userName + '.' + @objName + ''', ''dbo'''
print @outStr
print 'go'
fetch next from chObjOwnerCur into @userName, @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0

SQL injection

2008-12-07T20:43:00.000-08:00

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

Forms of SQL injection vulnerabilities

Incorrectly filtered escape characters
This form of SQL injection occurs when user input is not filtered for escape characters and is then passed into a SQL statement. This results in the potential manipulation of the statements performed on the database by the end user of the application.

The following line of code illustrates this vulnerability:

statement = "SELECT * FROM users WHERE name = '" + userName + "';"

This SQL code is designed to pull up the records of a specified username from its table of users. However, if the "userName" variable is crafted in a specific way by a malicious user, the SQL statement may do more than the code author intended. For example, setting the "userName" variable as

a' or 't'='t

renders this SQL statement by the parent language:

SELECT * FROM users WHERE name = 'a' OR 't'='t';

If this code were to be used in an authentication procedure then this example could be used to force the selection of a valid username because the evaluation of 't'='t' is always true.

While most SQL Server implementations allow multiple statements to be executed with one call, some SQL APIs such as php's mysql_query do not allow this for security reasons. This prevents hackers from injecting entirely separate queries, but doesn't stop them from modifying queries. The following value of "userName" in the statement below would cause the deletion of the "users" table as well as the selection of all data from the "data" table (in essence revealing the information of every user):

a';DROP TABLE users; SELECT * FROM data WHERE name LIKE '%

This input renders the final SQL statement as follows:

SELECT * FROM Users WHERE name = 'a';DROP TABLE users; SELECT * FROM DATA WHERE name LIKE '%';

Incorrect type handling
This form of SQL injection occurs when a user supplied field is not strongly typed or is not checked for type constraints. This could take place when a numeric field is to be used in a SQL statement, but the programmer makes no checks to validate that the user supplied input is numeric. For example:

statement := "SELECT * FROM data WHERE id = " + a_variable + ";"

It is clear from this statement that the author intended a_variable to be a number correlating to the "id" field. However, if it is in fact a string then the end user may manipulate the statement as they choose, thereby bypassing the need for escape characters. For example, setting a_variable to

1;DROP TABLE users

will drop (delete) the "users" table from the database, since the SQL would be rendered as follows:

SELECT * FROM DATA WHERE id=1;DROP TABLE users;

Magic String
The magic sting is a simple string of SQL used primarily at login pages. The magic string is

'OR''='

When used at a login page, you will be logged in as the user on top of the SQL table.

Vulnerabilities inside the database server
Sometimes vulnerabilities can exist within the database server software itself, as was the case with the MySQL server's mysql_real_escape_string() function[1]. This would allow an attacker to perform a successful SQL injection attack based on bad Unicode characters even if the user's input is being escaped.

Blind SQL Injection
Blind SQL Injection is used when a web application is vulnerable to SQL injection but the results of the injection are not visible to the attacker. The page with the vulnerability may not be one that displays data but will display differently depending on the results of a logical statement injected into the legitimate SQL statement called for that page. This type of attack can become time-intensive because a new statement must be crafted for each bit recovered. There are several tools that can automate these attacks once the location of the vulnerability and the target information has been established.[2]

Conditional Responses
One type of blind SQL injection forces the database to evaluate a logical statement on an ordinary application screen.

SELECT booktitle FROM booklist WHERE bookId = 'OOk14cd' AND 1=1

will result in a normal page while

SELECT booktitle FROM booklist WHERE bookId = 'OOk14cd' AND 1=2

will likely give a different result if the page is vulnerable to a SQL injection. An injection like this will prove that a blind SQL injection is possible, leaving the attacker to devise statements that evaluate to true or false depending on the contents of a field in another table.[3]

Conditional Errors
This type of blind SQL injection causes a SQL error by forcing the database to evaluate a statement that causes an error if the WHERE statement is true. For example,

SELECT 1/0 FROM users WHERE username='Ralph'

the division by zero will only be evaluated and result in an error if user Ralph exists.

Time Delays
Time Delays are a type of blind SQL injection that cause the SQL engine to execute a long running query or a time delay statement depending on the logic injected. The attacker can then measure the time the page takes to load to determine if the injected statement is true.

Preventing SQL Injection
To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, parameterized statements must be used (preferred), or user input must be carefully escaped or filtered.

Using Parameterized Statements
In some programming languages such as Java and .NET parameterized statements can be used that work with parameters (sometimes called placeholders or bind variables) instead of embedding user input in the statement. In many cases, the SQL statement is fixed. The user input is then assigned (bound) to a parameter. This is an example using Java and the JDBC API:

PreparedStatement prep = conn.prepareStatement("SELECT * FROM USERS WHERE USERNAME=? AND PASSWORD=?");
prep.setString(1, username);
prep.setString(2, password);

Similarly, in C#:

using (SqlCommand myCommand = new SqlCommand("SELECT * FROM USERS WHERE USERNAME=@username AND PASSWORD=HASHBYTES('SHA1', @password)", myConnection))
{
myCommand.Parameters.AddWithValue("@username", user);
myCommand.Parameters.AddWithValue("@password", pass);

myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader())
...................
}

In PHP version 5 and MySQL version 4.1 and above, it is possible to use prepared statements through vendor-specific extensions like mysqli[4]. Example[5]:

$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $db -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();

In ColdFusion, the CFQUERYPARAM statement is useful in conjunction with the CFQUERY statement to nullify the effect of SQL code passed within the CFQUERYPARAM value as part of the SQL clause.[6] [7]. An example is below.

SELECT *
FROM COMMENTS
WHERE COMMENT_ID =

Enforcing the Use of Parameterized Statements
There are two ways to ensure an application is not vulnerable to SQL injection: using code reviews (which is a manual process), and enforcing the use of parameterized statements. Enforcing the use of parameterized statements means that SQL statements with embedded user input are rejected at runtime. Currently only the H2 Database Engine supports this feature.

Using Escaping
A straight-forward, though error-prone way to prevent injections is to escape dangerous characters. One of the reasons for it being error prone is that it is a type of blacklist which is less robust than a whitelist. For instance, every occurrence of a single quote (') in a parameter must be replaced by two single quotes ('') to form a valid SQL string literal. In PHP, for example, it is usual to escape parameters using the function mysql_real_escape_string before sending the SQL query:

$query = sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'",
mysql_real_escape_string($Username),
mysql_real_escape_string($Password));
mysql_query($query);

However, escaping is error-prone as it relies on the programmer to escape every parameter. Also, if the escape function fails to handle a special character correctly, an injection is still possible.

Find Out The Recovery Model For Your Database

2008-12-07T20:34:00.000-08:00

You want to quickly find out what the recovery model is for your database but you don’t want to start clicking and right-clicking in SSMS/Enterprise Manager to get that information. This is what you can do, you can use databasepropertyex to get that info. Replace ‘msdb’ with your database name

SELECT DATABASEPROPERTYEX(‘msdb’,‘Recovery’)

What if you want it for all databases in one shot? No problem here is how, this will work on SQL Server version 2000

SELECT name,DATABASEPROPERTYEX(name,‘Recovery’)
FROM sysdatabases

For SQL Server 2005 and up, you should use the following command

SELECT name,recovery_model_desc
FROM sys.databases

What is deferred name resolution and why do you need to care?

2008-12-07T20:28:00.000-08:00

DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
SELECT 1 AS VALUE INTO #temptable
END
ELSE
BEGIN
SELECT 2 AS VALUE INTO #temptable
END

SELECT * FROM #temptable –what does this return

This is the error you get
Server: Msg 2714, Level 16, State 1, Line 12
There is already an object named ‘#temptable’ in the database.

You can do something like this to get around the issue with the temp table

DECLARE @x INT

SET @x = 1

CREATE TABLE #temptable (VALUE INT)
IF (@x = 0)
BEGIN
INSERT #temptable
SELECT 1
END
ELSE
BEGIN
INSERT #temptable
SELECT 2
END

SELECT * FROM #temptable –what does this return

So what is thing called Deferred Name Resolution? Here is what is explained in Books On Line

When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the syscomments system table.

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

In the resolution stage, Microsoft SQL Server 2000 also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.

So what is happening is that beginning with SQL server 7 deferred name resolution was enabled for real tables but not for temporary tables. If you change the code to use a real table instead of a temporary table you won’t have any problem
Run this to see what I mean

DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
SELECT 1 AS VALUE INTO temptable
END
ELSE
BEGIN
SELECT 2 AS VALUE INTO temptable
END

SELECT * FROM temptable –what does this return

What about variables? Let’s try it out, run this

DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
DECLARE @i INT
SELECT @i = 5
END
ELSE
BEGIN
DECLARE @i INT
SELECT @i = 6
END

SELECT @i

And you get the follwing error
Server: Msg 134, Level 15, State 1, Line 13
The variable name ‘@i’ has already been declared. Variable names must be unique within a query batch or stored procedure.

Now why do you need to care about deferred name resolution? Let’s take another example
create this proc

CREATE PROC SomeTestProc
AS
SELECT dbo.somefuction(1)
GO

CREATE FUNCTION somefuction(@id INT)
RETURNS INT
AS
BEGIN
SELECT @id = 1
RETURN @id
END
Go

now run this

SP_DEPENDS ’somefuction’

result: Object does not reference any object, and no objects reference it.

Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won’t be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

SELECT specific_name,*
FROM information_schema.routines
WHERE object_definition(OBJECT_ID(specific_name)) LIKE ‘%somefuction%’
AND routine_type = ‘procedure’

How Do You Check If A Temporary Table Exists In SQL Server

2008-12-07T20:17:00.000-08:00

How do you check if a temp table exists?

You can use IF OBJECT_ID(’tempdb..#temp’) IS NOT NULL Let’s see how it works

–Create table
USE Norhtwind
GO

CREATE TABLE #temp(id INT)

–Check if it exists
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END

–Another way to check with an undocumented optional second parameter
IF OBJECT_ID(‘tempdb..#temp’,‘u’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END

–Don’t do this because this checks the local DB and will return does not exist
IF OBJECT_ID(‘tempdb..#temp’,‘local’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END

–unless you do something like this
USE tempdb
GO

–Now it exists again
IF OBJECT_ID(‘tempdb..#temp’,‘local’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END

–let’s go back to Norhtwind again
USE Norhtwind
GO

–Check if it exists
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END

now open a new window from Query Analyzer (CTRL + N) and run this code again

–Check if it exists
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END

It doesn’t exist and that is correct since it’s a local temp table not a global temp table

Well let’s test that statement

–create a global temp table
CREATE TABLE ##temp(id INT) –Notice the 2 pound signs, that’s how you create a global variable

–Check if it exists
IF OBJECT_ID(‘tempdb..##temp’) IS NOT NULL
BEGIN
PRINT ‘##temp exists!’
END
ELSE
BEGIN
PRINT ‘##temp does not exist!’
END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

–Check if it exists
IF OBJECT_ID(‘tempdb..##temp’) IS NOT NULL
BEGIN
PRINT ‘##temp exists!’
END
ELSE
BEGIN
PRINT ‘##temp does not exist!’
END

And yes this time it does exist since it’s a global table

Finding all data types in user tables

2008-12-04T20:14:00.000-08:00

This script queries sys.columns to get the entire list of columns and tables existing in the current database, then maps the columns datatype with a name from sys.systypes. The where clause filters the results for user created databases, less 'sysdiagrams', or you can use the commented out where clause to target a specific table.

This is a great way to hunt down various data types and make sure different development teams are on the same page and don't do silly things like having the data types on their tables not matching other tables and causing frustrations in forgetting to cast the values.

select object_name(c.object_id) "Table Name", c.name "Column Name", s.name "Column Type"
from sys.columns c
join sys.systypes s on (s.xtype = c.system_type_id)
where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')
-- where object_name(c.object_id) in (select name from sys.tables where name like 'TARGET_TABLE_NAME')

Increment a string

2008-12-03T20:11:00.000-08:00

It's good to create a serial number to tickets, or another serie from data type character.

declare @litere nvarchar(3)
declare @litera1 char(1)
declare @litera2 char(1)

set @litere='AQ'

select @litera1=substring(@litere,2,1)
select @litera2=substring(@litere,1,1)

if @litera1='Z'
begin
set @litera1='A'
set @litera2=char(ascii(substring(@litere,1,1))+1)
end

else
set @litera1=char(ascii(substring(@litere,2,1))+1)

select @litera1, @litera2
set @litere=@litera2+@litera1
select @litere

Writing Outer Joins in T-SQL

2008-11-30T23:13:00.000-08:00

Occasionally someone will ask for my help with a query and say that both a right outer join and a left outer join was tried, and still the expected results were not achieved. That made me realize that some developers do not completely understand outer joins and that an article explaining how to use them might help.

Inner Join Review
The most commonly used join is an INNER JOIN. This type of join combines rows from two tables only when they match on the joining condition. Usually the primary key from one table matches a foreign key on another table, but join conditions can be more complex than that.

(Note: Most of the information in this article can be applied to views as well as tables. For simplicity, the word "table" will be used to mean table or view unless stated otherwise. Keys are not defined on views, but the underlying table’s key column or columns are often included in the view. To keep things simple, let's assume that is the case.)

INNER JOIN will retrieve a results row only where there is a perfect match between the two tables in the join condition. You will also often see one row from one of the tables matching multiple rows in the other table. For example, one customer can have many orders. One order can have many order details. The data on the one side will be repeated for each row on the many side. The following query is an example showing how the information from the Sales.SalesOrderHeader is repeated on each matching row:

SELECT s.SalesOrderID, OrderDate,ProductID
FROM Sales.SalesOrderHeader AS s
INNER JOIN Sales.SalesOrderDetail AS d ON s.SalesOrderID = d.SalesOrderID
ORDER BY s.SalesOrderID, ProductID

Outer Join Introduction
OUTER JOIN is used to join two tables even if there is not a match. An OUTER JOIN can be used to return a list of all the customers and the orders even if no orders have been placed for some of the customers. A keyword, RIGHT or LEFT, is used to specify which side of the join returns all possible rows. I like using LEFT because it makes sense to me to list the most important table first. Except for one example demonstrating RIGHT OUTER JOIN, this article will use left joins. Just a note: the keywords INNER and OUTER are optional.

The next example returns a list of all the customers and the SalesOrderID for the orders that have been placed, if any.

SELECT c.CustomerID, s.SalesOrderID
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID

It uses the LEFT keyword because the Sales.Customer table is located on the left side and we want all rows returned from that table even if there is no match in the Sales.SalesOrderHeader table. This is an important point. Notice also that the CustomerID column is the primary key of the Sales.Customer table and a foreign key in the Sales.SalesOrderHeader table. This means that there must be a valid customer for every order placed. Writing a query that returns all orders and the customers if they match doesn’t make sense. The LEFT table should always be the primary key table when performing a LEFT OUTER JOIN.

If the location of the tables in the query are switched, the RIGHT keyword is used and the same results are returned:

SELECT c.CustomerID, s.SalesOrderID
FROM Sales.SalesOrderHeader s
RIGHT OUTER JOIN Sales.Customer c ON c.CustomerID = s.CustomerID

Notice that I didn’t change the join condition at all. It doesn’t matter which side of the equal sign the columns are listed; only where the tables are named is it important.

If I have a LEFT OUTER JOIN, what is returned from the table on the right side of the join where there is not a match? Each column from the right side will return a NULL. Try this query which lists the non-matching rows first:

SELECT c.CustomerID, s.SalesOrderID
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
ORDER BY s.SalesOrderID

By adding a WHERE clause to check for a NULL SalesOrderID, you can find all the customers who have not placed an order. My copy of AdventureWorks returns 66 customers with no orders:

SELECT c.CustomerID, s.SalesOrderID
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
WHERE s.SalesOrderID IS NULL

Occasionally, you will need to be more specific. How can you find all the customers who have not placed an order in 2002? There are several ways to solve this problem. You could create a view of all the orders placed in 2002 and join the view on the Sales.Customer table. Another option is to create a CTE, or Common Table Expression, of the orders placed in 2002. This example shows how to use a CTE to get the required results:

WITH s AS
( SELECT SalesOrderID, customerID
FROM Sales.SalesOrderHeader
WHERE OrderDate between '1/1/2002' and '12/31/2002'
)
SELECT c.CustomerID, s.SalesOrderID
FROM Sales.Customer c
LEFT OUTER JOIN s ON c.customerID = s.customerID
WHERE s.SalesOrderID IS NULL

My favorite technique to solve this problem is much simpler. Additional criteria, in this case filtering on the OrderDate, can be added to the join condition. The query joins all customers to the orders placed in 2002. Then the results are restricted to those where there is no match. This query will return exactly the same results as the previous, more complicated query:

SELECT c.CustomerID, s.SalesOrderID
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
and s.OrderDate between '1/1/2002' and '12/31/2002'
WHERE s.SalesOrderID IS NULL

Using Aggregates with Outer Joins
Aggregate queries introduce another pitfall watch out for. The following example is an attempt to list all the customers and the count of the orders that have been placed. Can you spot the problem?

SELECT c.CustomerID, count(*) OrderCount
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
GROUP BY c.CustomerID
ORDER BY OrderCount

Now the customers with no orders look like they have placed one order. That is because this query is counting the rows returned. To solve this problem, count the SalesOrderID column. NULL values are eliminated from the count.

SELECT c.CustomerID, count(SalesOrderID) OrderCount
FROM Sales.Customer c LEFT OUTER JOIN Sales.SalesOrderHeader s
ON c.CustomerID = s.CustomerID
GROUP BY c.CustomerID
ORDER BY OrderCount

Multiple Joins
Once more than two tables are involved in the query, things get a bit more complicated. When a table is joined to the RIGHT table, a LEFT OUTER JOIN must be used. That is because the NULL rows from the RIGHT table will not match any rows on the new table. An INNER JOIN causes the non-matching rows to be eliminated from the results. If the Sales.SalesOrderDetail table is joined to the Sales.SalesOrderHeader table and an INNER JOIN is used, none of the customers without orders will show up. NULL cannot be joined to any value, not even NULL.

To illustrate this point, when I add the Sales.SalesOrderDetail table to one of the previous queries that checked for customers without orders, I get back no rows at all.

SELECT c.CustomerID, s.SalesOrderID, d.SalesOrderDetailID
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
INNER JOIN Sales.SalesOrderDetail d ON s.SalesOrderID = d.SalesOrderID
WHERE s.SalesOrderID IS NULL

To get correct results, change the INNER JOIN to a LEFT JOIN.

SELECT c.CustomerID, s.SalesOrderID, d.SalesOrderDetailID
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
LEFT OUTER JOIN Sales.SalesOrderDetail d ON s.SalesOrderID = d.SalesOrderID
WHERE s.SalesOrderID IS NULL

What about additional tables joined to Sales.Customer, the table on the left? Must outer joins be used? If it is possible that there are some rows without matches, it must be an outer join to guarantee that no results are lost. The Sales.Customer table has a foreign key pointing to the Sales.SalesTerritory table. Every customer’s territory ID must match a valid value in Sales.SalesTerritory. This query returns 66 rows as expected because it is impossible to eliminate any customers by joining to Sales.SalesTerritory:

SELECT c.CustomerID, s.SalesOrderID, t.Name
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
INNER JOIN Sales.SalesTerritory t ON c.TerritoryID = t.TerritoryID
WHERE SalesOrderID IS NULL

Sales.SalesTerritory is the primary key table; every customer must match a valid territory. If you wanted to write a query that listed all territories, even those that had no customers, an outer join will be used. This time, Sales.Customers is on the right side of the join.

SELECT t.Name, CustomerID
FROM Sales.SalesTerritory t
LEFT OUTER JOIN Sales.Customer c ON t.TerritoryID =c.TerritoryID

SQL Server Connection Strings

2008-11-30T22:59:00.000-08:00

Some common and not so common connection strings for the .NET SqlConnection object. The article includes .NET sample code and some tricks to increase the supportability of your application.

Trusted Authentication
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI;

Trusted authentication uses the security credentials of the current user to make the connection to SQL Server. SQL Server uses Windows (or Active Directory) to validate the current user. ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2. ServerName can also be expressed as an IP address. SSPI stands for Security Support Provider Interface (in you were curious).

SQL Server Security Authentication

Data Source=ServerName; Initial Catalog=DatabaseName; User Id=UserName; Password=UserPassword;

In SQL Server authentication SQL Server stores the username and password. ServerName can be the name of a server or the name of a SQL Server instance such as Server1\Instance2. ServerName can also be expressed as an IP address.

Setting the Application Name
Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; Application Name=MyAppName;

I often set the Application Name when I construct connections strings. Whatever text you assign to Application Name will appear in a couple of different places:

* It will be displayed in Profiler under the Application Name column.
* It will be shown in the output of sp_who2 in the Program Name column.
* It will be shown in the Activity Monitor in the Application column. You can get to the Activity Monitor in SQL Server Management Studio by Management -> Activity Monitor.
* It will appear in the program_name column if you select from master.dbo.sysprocesses (for SQL Server 2000)
* It will appear int he program_name column if you select from sys.dm_exec_sessions (for SQL Server 2005 and later).

Setting the application name makes it very easy to find out what applications are issuing particular SQL statements against my database. Setting the application name can also lead to an increase in the number of connections to your SQL Server. Each client that uses connection pooling will create one pool inside each application per unique connection string. If you use multiple application names you have the possibility to increase the number of pools and thus the number of connections to SQL Server. I've always found it more beneficial to have the application name than to have a few less connections to my database servers.

Using MARS (Multiple Active Result Sets)

Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; MultipleActiveResultSets=True;

If you want to use MARS you'll need to enable it in the connection string.

Sample .NET code
There are two common ways to create a connection string in .NET. The first is to use an explicit connection string.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=L40; Initial Catalog=master; Integrated Security=SSPI;";

The second is to use the Connection String Builder object in .NET to construct a connection string.

SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = "L40";
csb.InitialCatalog = "master";
csb.IntegratedSecurity = true;

SqlConnection conn = new SqlConnection();
conn.ConnectionString = csb.ToString();

Table Information View -- No Cursors!

2008-11-27T20:11:00.000-08:00

One of the most frequent request I get is for a procedure that can return sp_SpaceUsed type information for every table in a database. This is easy enough to do with cursors and dynamic SQL, but after looking at how sp_SpaceUsed worked and how SMO gets the same information, I decided that I could write it without either.

Even better, one I wrote it though, I realized that it could easily be rewritten as a view. Now I could reuse it by joining it with other tables and views in new queries or procedures whenever I wanted. Enjoy!

/*
vwTableInfo - Table Information View

This view display space and storage information for every table in the database.
Columns are:
Schema
Name
Owner may be different from Schema)
Columns count of the max number of columns ever used)
HasClusIdx 1 if table has a clustered index, 0 otherwise
RowCount
IndexKB space used by the table's indexes
DataKB space used by the table's data

16-March-2008, RBarryYoung@gmail.com
*/
--CREATE VIEW vwTableInfo
-- AS
SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]
, Coalesce( ( select sum (spart.rows) from sys.partitions spart
where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date

FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

SQL Server Query Execution Plan Analysis

2008-11-23T20:17:00.000-08:00

In most cases, the query optimizer will analyze joins and JOIN the tables using the most efficient join type, and in the most efficient order. But not always. In the graphical query plan you will see icons that represent the different types of JOINs used in the query. In addition, each of the JOIN icons will have two arrows pointing to it. The upper arrow pointing to the JOIN icon represents the outer table in the join, and the lower arrow pointing to the JOIN icon represent the inner table in the join. Follow the arrows back to see the name of the tables being joined.

Sometimes, in queries with multiple JOINs, tracing the arrow back won't reveal a table, but another JOIN. If you place the cursor over the arrows pointing to the upper and lower JOINs, you will see a popup window that tells you how many rows are being sent to the JOIN for processing. The upper arrow should always have fewer rows than the lower arrow. If not, then the JOIN order selected by the query optimizer might be incorrect (see more on this below).

First of all, let's look at JOIN types. SQL Server can JOIN a table using three different techniques: nested loop, hash, and merge. Generally, the fastest type of join in a nested loop, but if that is not feasible, then a hash JOIN or merge JOIN is used (as appropriate), both of which tend to be slower than the nested JOIN.

When very large tables are JOINed, a merge join, not a nested loop join, may be the best option. The only way to know is to try both and see which one is the most efficient.

If a particular query is slow, and you suspect it may be because the JOIN type is not the optimum one for your data, you can override the query optimizer's choice by using a JOIN hint. Before you use a JOIN hint, you will want to take some time to learn about each of the JOIN types and how each one works. This is a complicated subject, beyond the scope of this tip.

JOIN order is also selected by the query optimizer, which it trying to select the most efficient order to JOIN tables. For example, for a nested loop join, the upper table should be the smaller of the two tables. For hash joins, the same is true; the upper table should be the smaller of the two tables. If you feel that the query optimizer is selecting the wrong order, you can override it using JOIN hints.

In many cases, the only way to know for sure if using a JOIN hint to change JOIN type or JOIN order will boost or hinder performance is to give them a try and see what happens. [7.0, 2000, 2005]

SQL Server Full-Text Search Performance Tuning and Optimization

2008-11-20T20:10:00.000-08:00

If you are using the SQL Server Full-Text Search service, Microsoft recommends these two settings for optimum performance:

* The virtual memory (PAGEFILE.SYS file) setting for your operating system should be set to an amount equal to 3 times the amount of physical RAM in the server. If you have a non-dedicated SQL Server (a server running applications in addition to SQL Server) then you will want to add the virtual memory needs of these other applications to the amount calculated above.

* The SQL Server MAX SERVER MEMORY setting should be set manually (dynamic memory allocation is turned off) so that enough virtual memory is left for the Full-Text Search service to run. To achieve this, select a MAX SERVER MEMORY setting that once set, leaves enough virtual memory so that the Full-Text Search service is able to access an amount of virtual memory equal to 1.5 times the amount of physical RAM in the server. This will take some trial and error to achieve this setting.

To find out how much virtual memory is being used by SQL Server and the Full-Text Search Service, you can use the Task Manager. By default, the Task Manager does not display the amount of virtual memory used by a process. To see this number in Task Manager, you must first go to the "Processes" tab. Once there, select "View", and then "Select Columns". From the "Select Columns" dialog box, click on "Virtual Memory Size", then "OK". Now you will be able to see the amount of virtual memory size used by each process on your server using Task Manager. Use this information to help you tune your server for use with the Full-Text Search service. [7.0, 2000]

Reducing SQL Server Locks

2008-11-19T20:24:00.001-08:00

Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure. This provides two benefits that help to reduce blocking locks.

First, it limits the client application and SQL Server to communications before and after the transaction, thus forcing any messages between the client and the server to occur at a time other than when the transaction is running (reducing transaction time).

Second, it prevents the user from leaving an open transaction (holding locks open) because the stored procedure forces any transactions that it starts to complete or abort. [6.5, 7.0, 2000, 2005]

Avoid cursors in SQL Server with these methods to loop over records

2008-11-18T22:18:00.000-08:00

Many articles have beaten up on SQL Server cursors -- database objects that manipulate data in a set on a row-by-row basis -- and I want to add my name to the list of people who wish cursors had never been introduced. But, unfortunately, cursors are a fact of life. Problems with cursors include extending locks, their inability to cache execution plans and CPU/RAM overhead. Many T-SQL programmers and DBAs do not know how to successfully loop over records without the need for cursors. In this tip, I'll share some alternatives to cursors that provide looping functionality.

Method 1: Temp table with identity column

In the first approach, we will use a temp table with an identity column added to allow for row-by-row selection. If you're performing an INSERT/UPDATE/DELETE, be sure to use the explicit transactions. This vastly reduces the load on your log file by committing per loop, and it prevents huge rollbacks in the case of failure.

set nocount on
declare @i int --iterator
declare @iRwCnt int --rowcount
declare @sValue varchar(100)

set @i = 1 --initialize

create table #tbl(ID int identity(1,1), Value varchar(100))

insert into #tbl(Value)
select name
from master..sysdatabases (nolock)

set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work

create clustered index idx_tmp on #tbl(ID) WITH FILLFACTOR = 100
/*

Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. Since you know the data in this column, you can set the fill factor to 100% to get the best read times.

*/
while @i <= @iRwCnt
begin
select @sValue = Value from #tbl where ID = @i

--begin tran
print 'My Value is ' + @sValue --replace with your operations on this value
--commit tran

set @i = @i + 1
end
drop table #tbl

Method 2: Temp table without ID

In the second approach, we use a temp table without an identity column and simply grab the top row to process, then loop until we find no more rows to process. If you're performing an INSERT/UPDATE/DELETE, again, be sure to use the explicit transactions to vastly reduce the load on your log file by committing per loop, which prevents huge rollbacks in the case of failure.

set nocount on

declare @i int --iterator
declare @iRwCnt int --rowcount
declare @sValue varchar(100)
set @i = 1 --initialize

create table #tbl(Value varchar(100))

insert into #tbl(Value)
select name
from master..sysdatabases (nolock)

set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work

create clustered index idx_tmp on #tbl(Value) WITH FILLFACTOR = 100
/*

Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. Since you know the data in this column, you can set the fill factor to 100% to get the best read times.

*/

while @iRwCnt > 0
begin
select top 1 @sValue = Value from #tbl
set @iRwCnt = @@ROWCOUNT --ensure that we still have data

if @iRwCnt > 0
begin

--begin tran
print 'My Value is ' + @sValue --replace with your operations on this value
--commit tran

delete from #tbl where value = @sValue --remove processed record
end
end

drop table #tbl

Method 3: Selecting a comma-delimited list of items

When most developers/DBAs are asked to come up with a list of comma-delimited values from a table, they typically use a cursor or temp table (as above) to loop through the records. However, if you do not need to use a GROUP BY or an ORDER BY, then you can use the method below that operates in batch to handle the task. This cannot be used with GROUP BY DISTINCT, or ORDER BY, because of how SQL Server handles those operations.

Basically, this takes a given variable, and for every row in the table it adds the current value to the variable along with a comma.

declare @vrs varchar(4000)
declare @sTbl sysname
set @sTbl = 'TableName'
set @vrs = ''
select @vrs = @vrs + ', ' + name from syscolumns where id = (select st.id from sysobjects as st where name = @sTbl) order by colorder
set @vrs = right(@vrs, len(@vrs)-2)
print @vrs

This article gives you some good reasons why cursors in SQL Server should be avoided as well as some alternatives that give you looping functionality. Keep in mind that SQL Server is designed around batch processing, so the less you loop, the faster your system will run.

Egoless programming: The path to better code

2008-11-18T21:06:00.000-08:00

Egoless programming is a way for developers to distance themselves emotionally from their work so that they become open to criticism, often from individuals with less perceived ability. Enter egoless programming.

Some Guidlines

1. Understand and accept that you will make mistakes.
The point is to find them early, before they make it into production. Fortunately, except for the few of us developing rocket guidance software at JPL, mistakes are rarely fatal in our industry. We aren’t surgeons; we can learn, laugh, and move on.

2. You are not your code.
Remember, the entire point of a review is to find problems, and problems will be found. Don’t take it personally when a problem is uncovered.

3. No matter how much karate you know, someone else will always know more.
This fact kept the samurai from indiscriminately attacking people in Imperial Japan. In our less violent times, such an individual can teach you some new moves if you ask. There will always be people who know more than you. Seek and accept input from others, even when you think it’s not needed.

4. Don’t rewrite other programmers’ code.
There’s a fine line between “fixing other programmers’ code” and “rewriting other programmers’ code.” The former implies that a bug or other functionality problem exists and needs to be fixed and can also refer to correcting gross readability problems. The latter, however, refers to changes made to code for the sake of style. Programmers fresh from college are often guilty of this. Things like renaming variables, use of a different construct, recommenting, or gratuitous reformatting of white space fall into this category. Such activities, even with the purest of motive, are high hubris and detrimental to team mentality.

5. Treat people who know less than you with respect, deference, and patience.
Nontechnical people who deal with developers on a regular basis almost universally hold the opinion that we are prima donnas at best and crybabies at worst. Becoming angry only reinforces this perception and teaches people to avoid asking questions. This can only harm your work in the long run.

6. The only constant in the world is change.
Be open to it and accept it with a smile. Look at each change to your requirements, platform, or tool as a new challenge, not as some serious inconvenience to be fought.

7. The only true authority stems from knowledge, not from position.
Knowledge engenders authority, and authority engenders respect—so if you want respect in an egoless environment, cultivate knowledge.

8. Fight for what you believe but gracefully accept defeat.
Understand that sometimes your ideas will be overruled. Even if you do turn out to be right, don’t take revenge or say, “I told you so” more than a few times at most, and don't make your dearly departed idea a martyr or rallying cry.

9. Don’t be “the guy in the room.”
Don’t be the guy coding in the dark office emerging only to buy cola. The guy in the room is out of touch, out of sight, and out of control and has no place in an open, collaborative environment.

Following these rules will prevent needless eggshell walking by your coworkers and will allow them to get back to the one activity everyone in this profession enjoys: creating great things with their computers.

"Nothing has changed" - Determining when a procedure has been altered

2008-11-17T00:55:00.000-08:00

Someone need to determine when a stored procedure was last altered. Without having implemented a series of DDL triggers, how can this be accomplished?

In Microsoft SQL Server, you can easily retrieve this information from the sys.procedures catalog view. The following query demonstrates this.

SELECT
[name]
,modify_date
,create_date
,*
FROM
sys.procedures

Of course you can take it a step further by limiting the results to a period of time where you know that no changes should have been made. For example, the following query lists all stored procedures that have been changed since August 1, 2007 (the time I last visited this client).

SELECT
[name]
,modify_date
,create_date
,*
FROM
sys.procedures
WHERE
modify_date > '2008-10-01'

Although, using this technique will not allow me to identify who made the change, I can at least determine that a change has taken place.
Cheers!

Reading the transaction log

2008-11-17T00:45:00.000-08:00

SELECT *
FROM ::fn_dblog(DEFAULT, DEFAULT) AS l
INNER JOIN sysobjects AS so ON so.name = l.[transaction name]

SELECT so.name AS ObjectName,
so.type AS ObjectType,
MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime
FROM ::fn_dblog(DEFAULT, DEFAULT) l
inner join sysobjects so on so.name = l.[transaction name]
--where so.type = 'u'
GROUP BY so.name,
so.type
ORDER BY so.name,
so.type

SQL Server Join Hints

2008-11-16T22:41:00.000-08:00

JOIN hints can be used in a query to specify the type of JOIN the Query Optimizer is to use for the execution plan. The JOIN options are:

* Loop
* Merge
* Hash

The syntax for a JOIN hint is (using an INNER JOIN as an example):

FROM table_one INNER [LOOP | MERGE | JOIN] JOIN table_two

Here's an example:

FROM header_table INNER LOOP JOIN detail_table

As you can see, the JOIN hint is between the type of JOIN (in this example, INNER) and the JOIN keyword. Only one JOIN hint can be used per JOIN in a query. In addition, JOIN hints can only be used when the ANSI JOIN syntax is used, not the older Microsoft JOIN syntax.

The syntax above is not the only option to add a JOIN hint to a query. In addition, you can also use OPTION clause. Using the OPTION clause specifies that the hint be used throughout all of the query. While multiple hints can be added to the OPTION clause, each query hint can be used only once. In addition, only one OPTION clause can be used per query.

Here's an example of using the OPTION clause:

OPTION (INNER) or OPTION (MERGE) or OPTION (HASH)

The Query Optimizer always tries to identify the fastest way to JOIN tables. The fastest JOIN method is the Loop JOIN, followed by the Merge and the Hash JOIN. While the Query Optimizer always tries to perform a Loop JOIN if possible, it is not always possible, and one of the other two types may have to be used.

Before you attempt to use JOIN hint to specify a JOIN type, you should first take a look at your query, and the indexing of the relevant tables, to see if you can find a way, other than using a hint, to induce the Query Optimizer to use a Loop JOIN. Keep in mind that if you specify a Loop JOIN hint for a JOIN that is currently using a HASH JOIN, that you may get worse, not better performance. So always test JOIN hints to see what their end result in performance really is. [7.0, 2000]

Reducing SQL Server Deadlocks

2008-11-13T20:54:00.001-08:00

When a deadlock occurs, by default, SQL Server choose a deadlock "victim" by identifying which of the two processes will use the least amount of resources to rollback, and then returns error message 1205.

But what if you don't like default behavior? Can you change it? Yes, you can, by using the following command:

SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

Where:

Low tells SQL Server that the current session should be the preferred deadlock victim, not the session that incurs the least amount of rollback resources. The standard deadlock error message 1205 is returned.

Normal tells SQL Server to use the default deadlock method.

@deadlock_var is a character variable specifying which deadlock method you want to use. Specify "3" for low, or "6" for normal.

This command is set a runtime for a specified user connection. [2000]

The Cost of Function Use In A Where Clause

2008-11-12T02:03:00.001-08:00

A common mistake made in writing SQL statements is to wrap filtering columns of a WHERE clause inside of a function. SQL server performance is very dependant on SQL's ability to properly use indexes when it retrieves records. The lifeblood of proper query plan creation is using very deterministic filtering logic on indexed columns (Some refer to this as Sargable Where clauses).

The biggest problem we see is when a filtering column is wrapped inside of a function, the query optimizer does not see the column and if an index exists on that column, the index likely will not be used (of course, just like anything else in our hi-tech world, this is not always the case).
Take this example

In the CadencedEventCustomer table, we have a nonclustered index on FullName1. The simple query below should use this index and should perform an index seek to find the qualifying records instantly (Seek = FAST).

Select
*
FROM
CadencedEventCustomer
WHERE
isNull(FullName1,'') = 'Ed Jones'

Our intention here (albeit incorrect) is to correctly handle records in which the FullName1 field is null. Our concern is that Null records will be missed (more on this later).

Since we are wrapping the FullName1 column in the IsNull function, the query optimizer doesn't see it and the index is not used.

Here is the query plan produced by SQL Server's query engine:

|--Index Scan(OBJECT:([CIPv3].[dbo].[CadencedEventCustomer].[nci_CadencedEventCustomer_FullName1]), WHERE:(isnull([CIPv3].[dbo].[CadencedEventCustomer].[FullName1],'bob')='Ed Jones'))

We see an Index Scan on the FullName1 index (the index exists, but a seek cannot be performed due to the function wrapping the indexed column). A Scan means the entire table is being searched from beginning to end until SQL happens to find all the records satisfying the condition. On a table with millions of records, this obviously takes a long time.

The subtree cost of this query is 10.58. Subtree cost is a relative cost to SQL Server indicating how difficult this query is for SQL Server to retrieve the data. The higher the relative cost, the more of a hit on performance (CPU, IO, RAM, etc). From my experience, anything in the double digit range is getting pretty spendy and will not perform well in a production environment. In this case, the cost is attributed to the inability of the optimizer to use an index which means SQL Server is having to do a full table scan to return all the data for the query result...OUCH.

If we remove the IsNull() function our query looks like this:

Select
*
FROM
CadencedEventCustomer
WHERE
FullName1 = 'Ed Jones'

This is a much cleaner WHERE clause and SQL Server will now see the indexed column and will use the index correctly:

|--Index Seek(OBJECT:([CIPv3].[dbo].[CadencedEventCustomer].[nci_CadencedEventCustomer_FullName1]), SEEK:([CIPv3].[dbo].[CadencedEventCustomer].[FullName1]='Ed Jones') ORDERED FORWARD)

Notice we now have a SEEK (Fast). The query subtree cost is now at 0.0093 !

Keep in mind, this behavior is true for ALL Functions (Not just IsNull). We should try very hard to not wrap filtering WHERE clause columns within a function.

Now....as for IsNull. The reason we are using IsNull() in the first place is to return records where the value is null in the column (I recommend not allowing nulls in the database, but that topic is for another article).

However, if we look closely at this WHERE clause, we can immediately see that the net result of our query (in this case) is identical whether we use the IsNull function or not.

Select
*
FROM
CadencedEventCustomer
WHERE
isNull(FullName1,'') = 'Ed Jones'

What this query says is return all records where the FullName1 = 'Ed Jones' and IF the FullName1 column in the database holds a Null Value assume that value is '' (an empty string instead of a Null value).

So....Null values will be replaced with '' which still does NOT Equal 'Ed Jones'. The net effect is that records with NULL will NOT be returned.
So if we change our query to this:

Select
*
FROM
CadencedEventCustomer
WHERE
FullName1 = 'Ed Jones'

We would get exactly the same result.

So the question is, when DO we need IsNull?

Well....if the IsNull function is replacing nulls with a value that satisfies the comparison and returns TRUE, then the IsNull function is more useful (still not needed as we'll see below).

Take this example:

Select
*
FROM
CadencedEventCustomer
WHERE
isNull(FullName1,'Ed Jones') = 'Ed Jones'

In this case, if we find Null values in the FullName1 column, we will replace them with 'Ed Jones'. Notice that 'Ed Jones' satisfies our comparison and will return true. So in a sense, this logic is forcing the Query to return Null values instead of filtering them out. In this case, we can argue that the IsNull function is useful.

Now, having said that, although the IsNull() logic is now useful, the problem is that the column is being hidden from the optimizer and we lose the index relief provided by the index.

We can rewrite this SQL statement to get the same result AND to still get index relief for the optimizer.

Essentially what we want is this:

Select All CadencedEventCustomers Where the FullName1 is 'Ed Jones' OR where the FullName1 is a null value.

So the correct rewrite to still obtain index relief is as follows:

Select
*
FROM
CadencedEventCustomer
WHERE
((FullName1 = 'Ed Jones') OR (FullName1 IS NULL))

Now we get the best of both worlds. We get the correct logic producing the results we want and SQL servers query engine can see the column so we get index relief.
A few examples other than IsNull()

I often see the use of Substring() and other string functions used in WHERE Clauses.
Take the following
WHERE SUBSTRING(MasterDealer.Name,4) = 'Ford'

This is effectively hiding the MasterDealer.Name from the optimizer and is negating the use of any index on the Name column.
The fix
WHERE MasterDealerName Like 'Ford%'

This produces the exact same result, yet also provides index relief.
Another example working with dates
WHERE DateDiff(mm,PlacedOnQueue,GetDate()) >= 30

Again, this is a common technique used when working with DateTime data. This is, again, hiding the PlacedOnQueue column from the optimizer.
The fix
WHERE PlacedOnQueue < DateAdd(mm,-30,GetDate())

The exact same results, yet this time we get index relief.

Keep in mind, this behavior holds true for all functions wrapping columns in your WHERE clause. So remember anytime you feel a need to wrap a WHERE clause filtering column within a function, try really hard of a way to rewrite the statement without function use.

Monitor Database Growth

2008-11-11T22:55:00.000-08:00

This code provides a way of monitoring the growth of all your databases within a single instance. The first part is the creation of a monitoring table with the initial load of current databases and sizes. The second part is the SQL that can be put in a scheduled job to automate the growth monitoring.

It is recommended that this job is run weekly or monthly, depending on how fast your databases grow. Also, the code was written for SQL 2005 but can easily be altered for SQL 2000 by changing sys.Master_Files to sysaltfiles and sys.databases to sysdatabases. Make sure to change your column names appropriately if you make this alteration!

Create initial table and checks initial data
If exists (Select name from sys.objects where name = 'DBGrowthRate' and Type = 'U')
Drop Table dbo.DBGrowthRate

Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize decimal(10,2), CurSize decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)

Drop table #TempDBSize

Select *
from DBGrowthRate

Code to run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate
from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))
- dgr.CurSize)) + ' MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
End

--Select *
--from DBGrowthRate
----Verifies values were entered

Drop table #TempDBSize2

Trace : Who has accessed my SQL 2005 server

2008-11-11T22:50:00.000-08:00

This script returns login information from the default trace created in SQL Server 2005. When the sys.server_principals data is null that would mean the login is allowed via a Windows Group.

sys.traces provides the information for the default trace such as the file path and the max files.

fn_trace_gettable returns the data from trace file(s) in table format.

sys.server_principals is the way you should access server logins in SQL Server 2005, replacing syslogins.

An important thing to note is that the default trace will create up to 100MB (5 20MB files) of event data and then begin wrapping. Also it creates a new file when ever the SQL Server is restarted so you may not have the full 100MB of data if you reboot or restart SQL Server often.

SELECT
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
Min(I.StartTime) as first_used,
Max(I.StartTime) as last_used,
S.principal_id,
S.sid,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(T.path, T.max_files) I LEFT JOIN
sys.server_principals S ON
CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 And
I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name

default trace enabled Option
Use the default trace enabled option to enable or disable the default trace log files. The default trace functionality provides a rich, persistent log of activity and changes primarily related to the configuration options.

To open the default trace log in the default location:
SELECT *
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)
GO

When set to 1, the default trace enabled option enables Default Trace. The default setting for this option is 1 (ON). A value of 0 turns off the trace.

The default trace enabled option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change the default trace enabled option only when show advanced options is set to 1. The setting takes effect immediately without a server restart.

Performance Tuning SQL Server's Configuration Settings

2008-11-11T02:10:00.000-08:00

The default network packet size for clients communicating with SQL Server is 4096 bytes. In most cases, this is the overall best setting. But in some special cases, this SQL Server configuration setting should be changed. If the client regularly transfers large amounts of text or image data to SQL Server, or performs large BCP or DTS operations, then increasing the default size may boost performance because the number of network packets is reduced. On the other hand, if your application only sends and receives small amounts of data, then a smaller packet size can boost responsiveness.

Keep in mind that client software can overrule this setting in SQL Server. Only try changing this setting if you are very familiar with network traffic analysis and have the ability to test the performance, before and after the change. [6.5, 7.0, 2000, 2005]

Shrinking databases

2008-11-10T03:53:00.001-08:00

“Order the pages, shuffle the pages.”

Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again and, especially if the data files are on independent drives, there’s little difference between space free on the drive or space free in the data file. There is also a more insidious reason for not shrinking a database.

Let’s take a very simple database (The creation code is at the end of the post). I have two tables, both with a tens of thousands of rows. Both tables have a clustered index on a uniqueidentifier and are heavily fragmented (>99%).

DBCC SHOWCONTIG(LargeTable1) -- 99.30%
BCC SHOWCONTIG(LargeTable2) -- 99.21%

To fix the fragmentation, rebuild both indexes. That fixes the fragmentation, but now the data file is using almost twice the space necessary.

DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)

So, shrink the database to release the wasted space back to the OS

DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free

That’s fixed the space issue. But now, have another look at those two indexes that were just rebuilt.

DBCC SHOWCONTIG(LargeTable1)
- Logical Scan Fragmentation ………………: 99.99%

DBCC SHOWCONTIG(LargeTable2)
- Logical Scan Fragmentation ………………: 7.08%

Oops. Not exactly a desired outcome.

When SQL shrinks a data file, it takes extents that are towards the end of the file and moves them to empty places further forward. It does this with no concern over logical order of pages or indexes. Net result, after shrinking a database, many of the indexes in that database will be badly fragmented.

For this reason mainly I always recommend that, especially for production databases, the data files get grown as necessary and not shrunk. The space that can be reclaimed from the data file is not worth what the shrink does to page ordering. Especially since, as production databases tend to do, the file will simply be growing again sometime in the future.

All too often I hear of maintenance plans that first rebuild all the indexes, then shrink the data files. That kind of maintenance is worse than useless. The index rebuild uses cpu and time to arrange indexes in logical order and in the process often grows the data file. The shrink then uses more time and cpu and often will leave the indexes more fragmented than they were before the rebuild.

Basically, if you’re going to rebuild indexes, don’t shrink the data files. If you’re going to shrink data files, either don’t waste time rebuilding indexes, or do them after the shrink.

Paul Randal wrote a very nice post on the downsides of shrink, entitled “Turn Auto Shrink Off!” Pretty much says it all.

Caveat: There are cases where shrinking data files does make sense. When a process created lots of tables for processing then dropped them again, after a massive archiving job, after changing data types in a table to release a large amount of wasted space (more on that another time). Just be aware of the effect of a shrink on the fragmentation of indexes.

Edit: Some more thoughts from Paul Randal on shrinking databases: Autoshrink. Turn it OFF!

Sample Code:

1. SET NOCOUNT ON
2. GO
3.
4. CREATE DATABASE TestingShrink
5. GO
6.
7. ALTER DATABASE TestingShrink SET RECOVERY SIMPLE
8. GO
9.
10. USE TestingShrink
11. GO
12.
13. Create Table LargeTable1 ( -- row size of ~700 (10 rows per page)
14. ID BIGINT,
15. SomeString CHAR(600),
16. Row_ID UNIQUEIDENTIFIER,
17. AValue NUMERIC(30,8),
18. RandomDate DATETIME
19. )
20.
21. Create Table LargeTable2 ( -- row size of ~700 (10 rows per page)
22. ID BIGINT,
23. SomeString CHAR(600),
24. Row_ID UNIQUEIDENTIFIER,
25. AValue NUMERIC(30,8),
26. RandomDate DATETIME
27. )
28. GO
29.
30. -- ensuring high fragmentation
31. CREATE CLUSTERED INDEX idx_Large1 on LargeTable1 (Row_ID)
32. CREATE CLUSTERED INDEX idx_Large2 on LargeTable2 (Row_ID)
33. GO
34.
35. DECLARE @i SMALLINT
36. SET @i = 0
37. WHILE (@i<8)
38. BEGIN
39. ;WITH DataPopulate (RowNo, Strng,Uniqueid,Num,ADate) AS (
40. SELECT 1 AS RowNo, 'abc' as Strng, NewID() AS Uniqueid, rand()*856542 AS Num, DATEADD(dd, FLOOR(RAND()*75454),'1753/01/01')
41. UNION ALL
42. SELECT rowNo+1, 'abc' as Strng, NewID() AS Uniqueid, rand(RowNo*25411)*856542 AS Num, DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753/01/01')
43. FROM DataPopulate WHERE RowNo<10000
44. )
45. INSERT INTO LargeTable1
46. SELECT * FROM DataPopulate
47. OPTION (MAXRECURSION 10000)
48.
49. ;WITH DataPopulate (RowNo, Strng,Uniqueid,Num,ADate) AS (
50. SELECT 1 AS RowNo, 'abc' as Strng, NewID() AS Uniqueid, rand()*856542 AS Num, DATEADD(dd, FLOOR(RAND()*75454),'1753/01/01')
51. UNION ALL
52. SELECT rowNo+1, 'abc' as Strng, NewID() AS Uniqueid, rand(RowNo*25411)*856542 AS Num, DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753/01/01')
53. FROM DataPopulate WHERE RowNo<10000
54. )
55. INSERT INTO LargeTable2
56. SELECT * FROM DataPopulate
57. OPTION (MAXRECURSION 10000)
58. SET @i = @i+1
59. END
60. GO
61.
62. DBCC SHOWCONTIG(LargeTable1) -- 99.30%
63. DBCC SHOWCONTIG(LargeTable2) -- 99.21%
64. DBCC showfilestats -- 2467 extents total, 2463 used (157 MB total, 256kb free)
65. GO
66. -- Rebuild the indexes. This should grow the database quite a bit.
67. Alter Index idx_Large1 on LargeTable1 rebuild
68. Alter Index idx_Large2 on LargeTable2 rebuild
69. go
70.
71. DBCC SHOWCONTIG(LargeTable1) -- 0%
72. DBCC SHOWCONTIG(LargeTable2) -- 1%
73. DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)
74. GO
75.
76. USE Master
77. go
78. DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free
79. go
80. use TestingShrink
81. GO
82.
83. DBCC ShowFileStats -- 1885 extents total, 1695 used (120 MB total, 12 MB free)
84. DBCC SHOWCONTIG(LargeTable1) -- 99.99%
85. DBCC SHOWCONTIG(LargeTable2) --7.08%
86. GO
87.
88. USE master
89. GO
90.
91. DROP DATABASE TestingShrink
92. GO

Vardecimal Storage Format

2008-11-07T01:21:00.000-08:00

In Service Pack 2, SQL Server 2005 adds a new storage format for numeric and decimal datatypes called vardecimal. Vardecimal is a variable-length representation for decimal types that can save unused bytes in every instance of the row. The biggest amount of savings come from cases where the decimal definition is large (like decimal(38,6)) but the values stored are small (like a value of 0.0) or there is a large number of repeated values or data is sparsely populated.

SQL Server 2005 also includes a stored procedure that can estimate the savings before you enable the new storage format.

master.dbo.sp_estimate_rowsize_reduction_for_vardecimal ‘tablename’

To enable vardecimal storage format, you need to first allow vardecimal storage on the database;

exec sys.sp_db_vardecimal_storage_format N'databasename', N'ON'

Once the database option is enabled, you can then turn on vardecimal storage at a table level using the following procedure;

exec sp_tableoption 'tablename', 'vardecimal storage format', 1

Vardecimal storage format presents an overhead due to the complexity inherent in variable length data processing. However in IO bound workloads, savings on IO bandwidth due to efficient storage can far exceed this processing overhead.

If you would like more information on this topic, updated SQL Server 2005 Books Online for Service Pack 2 contains extensive information on the new vardecimal format.

Figuring out the most popular queries in seconds

2008-11-06T21:38:00.000-08:00

Sql Server 2005 provides Dynamic Management Views(DMV) that can help save you a lot of work. One excellent and helpful DMV is sys.dm_exec_query_stats. In previous version of SQL Server to find out the highest impact queries on CPU or IO in system, you had to walk through a long set of analyses steps including getting aggregated information out of the data you collected from profiler.

With sys.dm_exec_query_stats, you can figure out many combinations of query analyses by a single query. Here are some of the examples;
•

Find queries suffering most from blocking –
(total_elapsed_time – total_worker_time)
•

Find queries with most CPU cycles –
(total_worker_time)
•

Find queries with most IO cycles –
(total_physical_reads + total_logical_reads + total_logical_writes)
•

Find most frequently executed queries –
(execution_count)
•

You can find more information on how to use dynamic management views for performance troubleshooting in the “SQL Server 2005 Waits and Queues” whitepaper located at:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx