About zero cost plans in SQL Server OK, first thing first, what is a “zero cost plan” in SQL Server? I did some digging around and found the following links, which I would encourage you to read before continuing with this post: http://msdn.microsoft.com/en-us/library/ms181055.aspx http://support.microsoft.com/kb/325607 If you’ve done this background reading, you should now be aware […]
Read More
SolarWinds Database Performance Analyzer can collect a count of the number of deadlocks to be displayed as a resource graph. For Ignite versions prior to 8.2, you can update two files in order to start collecting the number of deadlocks. Enabling DPA to Collect Deadlock Information The code below takes advantage of the system_health extended […]
Read More
The FILESTREAM data type is SQL Server’s principle BLOB storage technology. The FILESTREAM data type lets you combine native NTFS file system performance with relational database data integrity. It supports up to 16 TB files which maximum file size supported by the NTFS file systems and simultaneously provides you with full data consistency. The FILESTREAM […]
Read More
Combining Binary Large Object (BLOBs) with relational databases like SQL Server has always been like combining oil and water. They just don’t mix very well. BLOB objects like pictures, videos, text documents and xml documents don’t fit within the standard relational data types. SQL Server originally offered support for BLOBs using the TEXT, NTEXT and […]
Read More
Although adding indexes can help to optimize the performance of SQL statements, the addition of indexes comes at a significant cost. DML Impact Adding indexes to a table affects the performance of writes. This can be easily shown from the artist table used in this chapter. Looking at the current definition shows a large number of […]
Read More
Poorly written server-side code and a badly designed database will make any application run slower, but improving the performance of a slow running web application requires examination of the entire system, not just the database. A typical three-tier web application structure is shown in Figure 1. FIGURE 1. Web application process flow As shown in […]
Read More
In this article, we discuss a feature of the Oracle Database that you can include in your arsenal: materialized views. Originally called snapshots, materialized views were introduced in Oracle8i and are only available in the Enterprise Edition. Like a regular view, the data in a materialized view results from a query. However, the results of a […]
Read More
Improving performance (and by this we usually mean query performance) is always a hot item with database administrators and users. One of the best and easiest ways to boost performance is to take advantage of the parallel processing option offered by Oracle Database 12c (Enterprise Edition only). Using normal (that is, serial) processing, the data involved […]
Read More
If you ever ask a SQL Server DBA about most frequently faced performance issue in SQL Server, they would most likely say blocking or high CPU. A smart DBA would first confirm that high CPU is caused by SQL Server by looking at performance monitor. Once it is confirmed who is consuming high amount of […]
Read More
How many times you have heard this question: “Why is SQL Server consuming so much memory?” If you ask a DBA who knows the functionality of SQL Server, the answer would be, “That is an expected behavior! Limit max server memory of SQL Server if you are concerned.” One might then wonder why SQL Server doesn’t release […]
Read More
SQL Server 2014 introduced the new database engine enhancement called In-Memory OLTP. This feature uses new data structures which are optimized for in-memory access of table. People normally call it In-Memory database. In reality, we would like to call it partially in-memory database because SQL Server allows us to have few hot tables in-memory and […]
Read More
If you’re database professional, you continuously face the challenge of improving database performance. Throwing hardware at the problem is a common solution to slow performance. And this approach often focuses on procuring SSD storage, servers, or CPU. But is ‘throwing hardware at the problem’ the right solution or is it just a big gamble? We […]
Read More