SQL Server Articles

Categories : Performance | Audit | Business Intelligence | Clustering | Developer | Reporting | Windows Server | SQL Azure | Powershell | ASP.NET | Other .NET

Ownership Chains in SQL Server 2005

When multiple database objects access each other sequentially, the sequence is known as a “chain.” Although such chains have no independent existence, when SQL Server is traversing the links in a chain, it evaluates user permissions on the constituent objects differently than it would if it were accessing them separately. These differences have important implications […]

Working With Temporary Views Using Common Table Expression in SQL Server 2005

When working with SQL 2005, there are times when you need to perform several aggregate functions, or to define a view that needs to be stored only during the execution of a particular query. A Common Table Expression (CTE) does just that; it helps in the definition and manipulation of query related results that need […]

Scripts for Backup SLA Report

########## Bkp_sla_main.bat ###################### @echo OFF @if not “%ECHO%”==”” echo %ECHO% rem ******************************************* rem Script : Bkp_sla_main.bat rem Author : Neeraj Nagpal rem Date : 10/08/2004 rem ******************************************* rem Set Local scope and call main proc. setlocal & pushd & Set RET= set SCRIPTNAME=%~n0 set SCRIPTPATH=%~f0 set DBASQL=E:dbaSQL set DBAOUT=E:DBAout set DEBUG=1 if “%DEBUG%”==”1” (set TRACE=echo) […]

Optimizing Microsoft SQL Server 2000 Reporting Services: Performance and Access Reports from the Execution Log

About the Series This is the second article of the series, Optimizing Microsoft SQL Server Reporting Services, which appears concurrently with my other series. The primary focus of this series is the introduction of optimization concepts and practices for MSSQL Server Reporting Services (“Reporting Services”). The series is designed to provide hands-on application of the […]

How SQL Server Determines an Execution Plan Using Available Indexes and Statistics

As a SQL Server DBA, one of my tasks is to monitor the performance of our databases. Our primary database holds the data of over 18 million people. A web-based application queries the database with a variety of search options. When examining the performance of these queries, most performed very well. But on the other […]

Automating Reindexing In SQL Server

In all OLTP environments, virtually all indexes will become fragmented over time. Nearly all UPDATE, INSERT or DELETE activity will cause your indexes to become less well organized than they were when they were first created. There will be more page splits, there will be a greater number of pages with less data on them, […]

Analyzing SQL Server 2000 Data Caching

One of SQL Server 2000’s main selling points is its ease-of-use and simplicity. You can install it out of the box and use it pretty much without making many configuration changes. In terms of the product’s use of memory, there are some settings you can change, for example, how much memory to use, whether to […]

Optimizing Microsoft SQL Server 2000 Reporting Services: Execution Log Reporting: Preparation as a Data Source

What We Will Need to Complete the Series Tutorials To get the most out of the Optimizing MSSQL Server Reporting Services series, we need to have installed Microsoft SQL Server 2000 Reporting Services, together with Microsoft Visual Studio.NET (required to access Report Designer for report creation). Reporting Services ships with MSSQL Server 2000, and many […]

Identify SQL Server Hardware Bottlenecks

Performance Audit Checklist  Counter Name Average Minimum Maximum  Memory: Pages/sec  Memory: Available Bytes  Physical Disk: % Disk time  Physical Disk: Avg. Disk Queue Length  Processor: % Processor Time  System: Processor Queue Length  SQL Server Buffer: Buffer Cache Hit Ratio  SQL Server General: User Connections Enter your results in the table above.   Use Performance Monitor to Help […]

Does SQL Server 2005 Clustering Really Make Sense for My Organization’s Needs?

As the forum administrator for SQL-Server-Performance.Com, I see hundreds of questions from DBAs on SQL Server clustering. What always surprises me is how misunderstood clustering is. So exactly what do I, and Microsoft, mean when we refer to SQL Server 2005 clustering? Here’s a good working definition: Microsoft SQL Server 2005 clustering is a high-availability […]