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?

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.

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 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.

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).

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.

There are 3 major theoretical differences between temporary tables:

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.

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 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.

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.

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 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.

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.

A test-drive of technologies.