Introduced in SQL Server 2014, the Buffer Pool Extension (BPE) came to build up a new layer of cache, helping to maintain the clean pages accessible with less effort. When a page is copied from the disk into the memory, its first state is always Clean. The Buffer Pool (BP) is the place in system … More Buffer Pool Extension
Hard disks are slow; memory is fast. This is a fact of nature for anyone that works with computers. When we’re talking about performance and memory we’re talking about a problem how to write data from slow storage into fast memory. Once loaded, your data can perform very fast and only need to go back … More Page Life Expectancy
What is Parallelism? Parallelism means that SQL Server is able to run operators in an execution plan across multiple worker threads. The goal of parallelism is to improve the throughput of your queries. The first configuration option that influences parallelism in SQL Server is the so-called Cost Threshold for Parallelism: Cost Threshold for Parallelism – … More Parallelism. How to set up advanced options?
As you know SQL Server runs using a bunch of different services in order for it to operate. These can be monitored, started and stopped using SQL Server Configuration Manager or Windows Services, but this is not always the most convenient way of checking the services. Is there any way this can be done within … More Monitoring SQL Server Services
Sometimes there will be the requirement to delete million rows from a table, as it is hard to run a single Delete statement because it could eventually fill up your transaction log and may not be truncated from log until all the rows have been deleted and the statement is completed because it will be … More Delete millions of rows from table
You already know that deadlock is when two or more sessions inside of the database engine end up waiting for access to locked resources held by each other. In a deadlock situation, none of the sessions can continue to execute until one of those sessions releases its locks, so allowing the other session(s) access to … More Populate Deadlock Summary Information
SQL Server database indexes are created mainly to optimize performance. Different types of indexes such as clustered index, non-clustered index are put in place to make query process run faster. However over time, DML statements (insert, update, delete) cause information scattered across database hence resulting table or index fragmentation. Fragmentation happens when the data logical … More Index defragmentation with (MAXDOP = 4)