SqlServer Begin's

PageIOLatch PageIOLatch_* , this wait type keeps transferring data from disk to memory. This is caused due  to pages been evicted from the buffer cache.  The pageIOLatch waittype started appearing as the most long standing wait type on one of the servers. It wasn't Immediately obvious what caused the waittype to be such a huge problem.  Figure 1   Figure1 , depicts the insert and update operations and the corresponding wait types during the insert and update opearion. The waittype was appearing against  the insert query which was executed 150k times per a hour. The insert query was a victim in this case and the update query that was executed few seconds later was the cause.   The update statement looked pretty solid, it was updating the table based on the single parameter which was the clustered key of the table.  :( The impact  - Page life expectancy would start reducing when this wait type appeared. - Log flush rate per sec will be ver

Today i got a adhoc request from the marketing team to get some details of few customers.  i.e The frequency of a customer filing their GST returns.  Just so that i can relate to things lot more easily i have used the Adventureworks database to explain the query. Days before SQL 2012 this would have been a quite a bit of an effort. With the Lead function been available with SQL 2012 things are lot more easier.  "For XML" was used to loop tough the data set and grab the results to a single row while LEAD was used to get the next/previous record for the difference. Select CustomerID ,  OrderFrequency         from Sales.Customer SH          cross apply         (             select STUFF((                          (                             SELECT  ', ' + CAST(isnull(                                                         DATEDIFF(dd,OrderDate ,                                                         LEAD(SH1.OrderDate) OVER (