SQL Server Performance Tuning

I would like some input on the best, most efficient way to store images for use on a website.

Question I 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 […]

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?

Question I 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 […]

Does SQL Server support table partitioning?

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? Answer Even 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 […]

I’ve been under the impression that joins are costly and should be kept to a minimum. Is this true?

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 […]

Which is faster when using SQL Server 2000 or 2005, temp tables or the table datatype?

Generally speaking, if the data you are dealing with is not large, then the table datatype will often be faster than using a temp table. But if the amount of data is large, then a temp table most likely will be faster. Which method is faster is dependent on the amount of RAM in your […]

How do I estimate how large a database will grow?

QuestionBeing a developer, I am not an expert at SQL Server (I know just enough to get done what I need); however, many of our clients are asking us for growth estimations of our database. How much MBs/GBs will a database take up? Also, does SQL Server have the ability to estimate how big it […]

My application makes heavy use of temp tables. Should I be creating temp tables as needed, or should I be using a permanent table over and over instead?

Assuming everything is the same, both tables will produce very similar access speeds. If your application makes heavy use of temp tables, you might consider using a permanent table for these reasons:  A regular table already exists. A temp table has to be created, which takes time and overhead. For example, if you need to […]

I don’t know how to transfer an array to a table, and I even if I can, I don’t know if it will be any faster than using INSERT INTO?

Question I have an INSERT INTO SELECT query which appends about 500,000 rows of data to a table. The query takes about 22 minutes to run. I would like it to run in less than 5 minutes, if possible. One solution I thought of was to append the 500,000 rows of data to an array […]

Is there a practical limit to the size of a table I can pin into memory?

Question I have read about pinning frequently accessed tables into memory rather than let them being accessed from the disk all the time. But what I wish to know is if there is any cutoff limit for the size of a table that could make it a bad candidate for pinning? Answer By default, SQL […]