Query Optimization Tips

I am running an application that issues queries directly to the database from within its source code. I have noticed that the database server will get low on available memory and that CPU activity is higher than I would like. What could be the problem?

Most SQL Server installations are installed with the default collation which is case insensitive. Because of this it is difficult to determine the differences of a value in upper case vs. lower case. By using COLLATE clause we can force a different collation to be used when running a query and therefore affect the query output.

In SQL Server 2000 there was not a simple way to create cross-tab queries, but a new option in SQL Server 2005 has made this a bit easier. This new option in SQL Server 2005 is called PIVOT and makes the job of creating cross-tab queries much easier then in the past.

As far as data management and RDBMS rules are concerned, relational databases are the most adoptable way for maintaining data with a reasonable level of concurrency and while supporting data management activities such as backups, mass purges, changing data structures, etc. One problem is the difference in programming languages in traditional applications. The SQL (Structured Query Language) language is a declarative language that became the "Data Language" used for describing "what I need" and "where to fetch it from" in most organizations. OOP (Object Oriented Programming) languages became the most common practice among developers widely adopted by R&D organizations around the world. So how do we bridge the gap?

The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. MAXDOP is very beneficial in a number of circumstances, but what if you have a reporting like query that runs in an OLTP system that monopolizes much of the CPU and adversely affects typical OLTP transactions. How can you scale back the CPU usage just for this query?

Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. Unfortunately there are problems with both of these approaches if you are trying to query data on the fly. Another approach to this problem is using derived tables.

In SQL Server 2000 there was not a simple way to create cross-tab queries, but a new option in SQL Server 2005 has made this a bit easier. This new option in SQL Server 2005 is called PIVOT and makes the job of creating cross-tab queries much easier then in the past.

Most SQL Server installations are installed with the default collation which is case insensitive. Because of this it is difficult to determine the differences of a value in upper case vs. lower case. By using COLLATE clause we can force a different collation to be used when running a query and therefore affect the query output.

Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. Unfortunately there are problems with both of these approaches if you are trying to query data on the fly. Another approach to this problem is using derived tables.

The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. MAXDOP is very beneficial in a number of circumstances, but what if you have a reporting like query that runs in an OLTP system that monopolizes much of the CPU and adversely affects typical OLTP transactions. How can you scale back the CPU usage just for this query?

As far as data management and RDBMS rules are concerned, relational databases are the most adoptable way for maintaining data with a reasonable level of concurrency and while supporting data management activities such as backups, mass purges, changing data structures, etc. One problem is the difference in programming languages in traditional applications. The SQL (Structured Query Language) language is a declarative language that became the "Data Language" used for describing "what I need" and "where to fetch it from" in most organizations. OOP (Object Oriented Programming) languages became the most common practice among developers widely adopted by R&D organizations around the world. So how do we bridge the gap?

I am running an application that issues queries directly to the database from within its source code. I have noticed that the database server will get low on available memory and that CPU activity is higher than I would like. What could be the problem?

I am running an application that issues queries directly to the database from within its source code. I have noticed that the database server will get low on available memory and that CPU activity is higher than I would like. What could be the problem?

As far as data management and RDBMS rules are concerned, relational databases are the most adoptable way for maintaining data with a reasonable level of concurrency and while supporting data management activities such as backups, mass purges, changing data structures, etc. One problem is the difference in programming languages in traditional applications. The SQL (Structured Query Language) language is a declarative language that became the "Data Language" used for describing "what I need" and "where to fetch it from" in most organizations. OOP (Object Oriented Programming) languages became the most common practice among developers widely adopted by R&D organizations around the world. So how do we bridge the gap?

The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. MAXDOP is very beneficial in a number of circumstances, but what if you have a reporting like query that runs in an OLTP system that monopolizes much of the CPU and adversely affects typical OLTP transactions. How can you scale back the CPU usage just for this query?

Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. Unfortunately there are problems with both of these approaches if you are trying to query data on the fly. Another approach to this problem is using derived tables.

Most SQL Server installations are installed with the default collation which is case insensitive. Because of this it is difficult to determine the differences of a value in upper case vs. lower case. By using COLLATE clause we can force a different collation to be used when running a query and therefore affect the query output.

In SQL Server 2000 there was not a simple way to create cross-tab queries, but a new option in SQL Server 2005 has made this a bit easier. This new option in SQL Server 2005 is called PIVOT and makes the job of creating cross-tab queries much easier then in the past.