SQL Server Performance Tuning
Yes, SQL Server’s performance can be affected by using NULLS in your database. There are several reasons for this. First, NULLS that appear in fixed length columns (CHAR) take up the entire size of the column. So if you have a column that is 25 characters wide, and a NULL is stored in it, then […]
QuestionI understand that I should avoid the use of SQL Server cursors in order to increase an application’s performance. Can you provide some ways that I might be able to avoid using cursors in my applications? AnswerA SQL Server cursor should only be considered in situations where you need to scroll through a set of […]
This is virtually no difference in performance between using SET or SELECT to assign values. In most cases you will want to use the ANSI standard, which says you should use SELECT. See the SQL Server Books Online for the exact syntax for both options.
QuestionI would like some input on the best, most efficient way to store images for use on a website. The two main options that I see are storing images as a BLOB in SQL Server, or storing the URL of the file in SQL Server, and actually storing the image file on a file share. […]
When creating temp tables within a stored procedure, is it good practice to drop them as soon as they are no longer required?
Maintaining temp tables uses up SQL Server resources, so the sooner you delete them after you are done using them, the better. For most SQL Servers, there is probably not much of a performance difference if you decide to delete them later than sooner, but on a very busy SQL Server that manages lots of […]
Since functions are not pre-compiled, is there any performance gain from using user-defined functions as opposed to stored procedures?
QuestionI work in a SQL Server environment where we have literally hundreds of stored procedures in one database that are utilized for an OLAP application, and likewise for an OLTP application. I have seen in many instances where a stored procedure is used solely because one can pass parameters to it and use these parameters […]
Question I have a table with over 40 million rows. I would like to partition the table base on the creation date. Does SQL Server support table partitioning? AnswerEven with the fastest hardware and optimally-written queries, there comes a point in the size of a table where there are just too many rows to handle quickly. […]
QuestionI have a “main” table named Property. This table has approximately 60 columns and over 1 million records (this record count will increase significantly over time). Of these 60 columns, approximately 25 are IDs which reference values in multiple different lookup tables. In any given view, query or store procedure, I end up joining 12-25 […]
We have recently added a second processor to our SQL Server box. How can I configure it to take advantage of the new processor?
You don’t have to do anything, assuming none of the default SQL Server settings have been changed. SQL Server will automatically take advantage of all the CPUs in your server. To find out how your SQL Server is currently configured, right-click on the server in Enterprise Manger or Management Studio, and select “Properties”. Next, select […]
QuestionI am the DBA of a SQL Server that has the memory setting configured to “Dynamically configure SQL Server memory, and the “Maximum Memory” set to 905MB out of a possible 1151MB. When I look at the Cache Size (pages) counter, it runs about 115MB. In addition, the Buffer Cache Hit Ratio counter is fine. Why […]