Posts

Showing posts from February, 2018

Performance Basics: Key lookup operator and query optimization

Image
One of the most basic things to improve performance in our queries is using indexes, but this doesn't mean that we can blindly create them for all our tables, or just thinking that it is enough to create them only for the columns we use to " search ", yes, this could be beneficial if our tables are now big enough, our system is not under a great I/O pressure. But for high concurrency systems, queries to large tables or more complex T-SQL statements, we could tune indexes a little bit more, and try to avoid disk reads as possible as we can do it. What we can do to improve performance is to avoid key lookups whenever possible. Key lookup icon The Key Lookup operator works this way: you have an index defined for your search argument, the index is used to locate the record, and then once located, an additional search is made to locate the record and retrieve the required columns in our query. So... for example, if the query is not used often and only to locate a

Considerations for dealing with big databases

Image
Stress everywhere, user wants everything "Para ayer" Photo by Bernard Goldbach If you are like me, and have SQL Server instances with databases of Terabytes of data, also known as very large database or VLDB, and you have to perform any kind of task on a reasonable time window, while minimizing the outage and keep the user happy with overall performance of the instance. I want to share you some tips to help to make your life easier with a variety of common tasks you can encounter.   Optimize disk space Maybe the most common task to deal with VLDs, a real headache if you have been handover the server with no further info and you have to optimize the disk space. SQL Files location Be aware of the location of each data and log file for your instance, find out how much space is taking each database file and if they are splitted across several files/folders. Next step is to order the datafiles following best practices, separate datafiles in different drives as