Contact  |  Site Map  |  Privacy      Call for more info
 
Latest articles

Articles


Become SQL performance Expert in few simple steps

Is it possible to answer that question in few seconds? May be you tried to look at the performance counters in PerfMon. There are about 1000 different counters with funny names. Say, if "Skipped Ghosted Records/sec" - is it good or bad? I don't know. This article can provide you with few practical pointers on how to avoid information overload.

SQL Server and VMware: A Potentially Fatal Combination

Virtual machines are becoming increasingly popular. They are often used to create 'safe sandboxes' on a physical box. Administrators are likely to put programs inside of these virtual machines and occasionally SQL servers. Unfortunately, putting SQL Server inside of a virtual machine is potentially a scenario for disaster!

Sudden Catastrophic Performance Degradation

Imagine that one day you find that your server is running 5 or even 10 times slower then before. What could cause that degradation? New version of the software? New server configuration? Some problems with hardware? It is very logical to check all this.

Multiple Databases or Multiple Instances?

Let's say you have 2 different applications, A and B, and only have one physical server. What is a best way to share that server between these 2 applications?

Performance of XML Indexes in MS SQL 2005 (and SQL 2008)

In this article we make a 'test drive' of XML indexes. We compare a traditional database schema with 2 master - Details tables versus one table where data is encoded in XML.

When SQL Server Query Optimizer Is Wrong

In most cases, SQL Server Optimizer generates optimal plans. It is impossible to compete with its internal knowledge of average disk access cost, record length or page fill ratio. But, there is one area where human expertise is always superior.

SQL Optimization

SQL optimization is a transformation of SQL statements, which produces exactly the same result, as an original query. This process requires a lot of creativity and intuition; it is more an Art than a Science.

Encrypted Columns and SQL Server Performance

For legal reasons, it is very important to encrypt table column with sensitive data, like SSNs. SQL server 2005 allows you to encrypt data using different algorithms using symmetric and asymmetric keys. Alternatively, you can also use password-based encryption (that password must be supplied by the client to encrypt/decrypt data).

SQL Server 2005 Data Versioning and Performance

Data versioning is one of the most important features of SQL 2005. Different data readers can obtain different values from the same record, like in Oracle. It can be achieved using SNAPSHOT ISOLATION LEVEL or DATABASE SNAPSHOTS.

In this study, we measure how these two new SQL Server 2005 features affect performance of data readers and data writers.

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

There are 3 major theoretical differences between temporary tables:

SQL Server Cache Hits Ratio and SQL Server Performance

As you know, one of the most important indicators of SQL server performance is a cache hits ratio. This is the percentage of pages that were found in the buffer pool without having to incur a read from disk.

Diagnosing Database Performance Problems with SQL Profiler and SQL Trace Analyzer

Many large-scale and complex enterprise applications are now developed and deployed using Microsoft SQL Server. However, these applications often suffer from poor performance and scalability because the focus of the development process is on functionality, while performance and scalability are dealt with as an afterthought.

SQL Server Tutorial: A SQL Server Experiment and its Findings

SQL server is not an ordinary application. Usually Windows applications use all the memory they need. If there is not enough memory on a server, some memory pages will be written into a page file.

SQL server application is different. It is aware of the memory conditions on a server. When it is configured to adjust memory automatically (Dynamically-configured memory), it can 'breathe', growing in size when there is a lot of free memory available, and reducing in size when 'pressure' from other program increases.

Let's check how it works. Experiments are conducted on a small computer with only 512Mb of physical memory.

How Values with Irregular Selectivity Impact SQL Server Database Performance

Values with irregular selectivity might dramatically degrade database performance. Well, the term 'values with irregular selectivity' may sound too scientific, but actually it is quite simple. I would even say that it is a regular thing.

Common Bad Practices in SQL Development that Lead to SQL Server Errors and Performance Problems

There is one common mistake, which is repeated again and again, that it is definitely in the beginning of the 'top ten' list of common causes of the performance problems.

CLR vs. T-SQL and Stored Procedures in SQL Server 2005

CLR or TSQL? That is the question.

More are more developers are struggling to find the right answer, but the answer really depends on the developer's individual needs.

For classic SQL tasks, the good old TSQL is recommended. On the other hand, CLR works best for calculations, parsing, image processing and other tasks that deal with a very limited amount of data.

We performed an experiment that defies the common perception that calculation tasks run several times faster when implemented in CLR form. For this experiment, we utilized a computer with Pentium 4 2.4GHZ processor and 1 Gb of RAM.

SQL 2005 vs. SQL 2000: SQL 2005 is not always faster

We continued on with our experiments that call almost empty procedures thousands of times over. The results are once again revealing.

Experiments are again done on a computer with Pentium 4 2.4GHz processor and 1Gb of RAM.

Disclaimer:

In many articles you will find the execution times of different queries. Please do not interpret these results scientifically or as official benchmarks. You will not even find the configuration of the computer used for these experiments because it is rather unimportant.

What is important is that query A is running 2 times slower then query B. On your computer, it could be different: 1.5 or even 3 times slower. Read the full disclaimer »