Avoid storing files in the database Makes database size very hard to manage. Backups and restore. Instead store files else where and store pointers to files in the database. Avoid storing business logic in the database Hard to maintain. Example: don’t use database to send emails or move files. Move business logic to application layer.... » read more
SET STATISTICS IO, TIME ON Statistics IO is going to give us the number of IO operations that SQL Server’s going to do to service our queries, and it’s going to break that down by logical reads from memory and physical reads from disk. The time statistic is just going to give us the actual... » read more
Estimate Plan vs Actual Plan Depends on the statistics your database has about your data. If you see a large differential between the estimated number of rows and the actual number of rows, it can be an indication that SQL Server does not have the proper statistics on the underlying tables and indexes that are... » read more
What happens when you issue a query to SQL Server. #1 SQL Server is going to parse the syntax of your query. SQL Server is going to check to see if your T-SQL that you’re submitting is valid and that all of the objects exist. This is a basic check just to make sure all... » read more
TempDB is used for many operations, such as user-created temporary objects, internal temporary objects and version stores and certain features like online re-indexing, multiple active record sets (MARS) and others. Because of all these uses, TempDB should be installed on a fast drive (like SSD) and even set to multiple disk drives. You should also... » read more
Removing Unnecessary Indexes Index maintenance requires lots of CPU and I/O. Every time we insert data into a database, SQL Server also needs to update the indexes, so it is better to remove them if they are not used. SQL Server Installation And Database Setup When setting up a database, we need to keep data... » read more
These four principles are referred to as ‘ACID’, and each letter is an acronym for one property of RDBMS systems that is non-negotiable for the sake of the integrity of the system. Atomic(ity) – The principle that each transaction is ‘all-or-nothing’, i.e. it either succeeds or it fails, regardless of external factors such as power... » read more
Some best practices for moving data from one table to another table. Use TRUNCATE instead of DELETE If you need to clear the data in a table, use TRUNCATE instead of DELETE. TRUNCATE lock the table instead of at each row. No triggers are activated and no logs are generated resulting in faster performance. Note:... » read more
One useful tool that SQL Server offers is the ability to see query execution plans. An execution plan shows you how SQL Server breaks down a query and also identified where issues might exist within the execution plan. Once you have identified statements that take a long time to complete, you can then look at... » read more
SQL Server does not have a “results cache” and the second execution is not “practically free.”SQL Server does have a “buffer cache” and the second execution is “faster, but not free.” The SQL Server buffer cache holds data pages in memory, in the exact form that they reside on disk. The second execution will not have to perform... » read more