Tag: performance

SQL – How to determine why a stored procedures sometimes takes many times longer than usual

We have a recently implemented data warehouse and reporting solution. Generally, the process of pulling the information together takes around 8 minutes. About once per day, one particular stored procedure takes much longer (~2 hours) than expected. There is no rhyme or reason to the time of day, and usually the next run will proceed […]

MSSQL performance issue when using nested query in where clause

MSSQL is doing something I don’t understand, and I hope to find an answer here. I have a small query that uses 2 sub-queries in the where clause: where TerminatedDateTime between @startdate and @enddate and Workgroup in (select distinct Workgroup from #grouping) and Skills in (select Skills from #grouping) The query runs fine, but when […]

Efficiently Shred Element Centric XML TSQL

I’m trying to shred an XML document with multiple levels of nested elements: <server> <name>111.111.11.11</name> <displayName>EVIL SERVER</displayName> <comment /> <logonCredentials inherit=”None”> <userName>user</userName> <domain>DOMAIN</domain> <password storeAsClearText=”True”>xxxxxxx</password> </logonCredentials> <connectionSettings inherit=”FromParent” /> <gatewaySettings inherit=”FromParent” /> <remoteDesktop inherit=”FromParent” /> <localResources inherit=”None”> <audioRedirection>2</audioRedirection> <audioRedirectionQuality>2</audioRedirectionQuality> <audioCaptureRedirection>0</audioCaptureRedirection> <keyboardHook>2</keyboardHook> <redirectClipboard>True</redirectClipboard> <redirectDrives>True</redirectDrives> <redirectPorts>False</redirectPorts> <redirectPrinters>False</redirectPrinters> <redirectSmartCards>False</redirectSmartCards> </localResources> <securitySettings inherit=”FromParent” /> <displaySettings inherit=”FromParent” /> </server> <server> […]

Alternative of Bitwise operation in sql query to achieve index seek

Let suppose there is a table having two fields “id” and “value”. I have created a non-clustured index on “value” field There is a parameter “@valueToCompare”. For example : MyTable Id(Int) Value(int) 1 9 2 11 3 13 4 7 5 8 6 20 @valueToCompare = 27 Now I want to write a query which […]

SQL Server stored procedure performance testing

I have a task to test stored procedures performance in SQL Server. My goal is to report the average time and standard deviation of the stored procedure’s execution time to the stakeholders. Realistic data input is a must here 🙂 My question: as I was trying to realistically stage the test I created a simple […]

sqlserver 2012 CPU Duration variation for single clustered index seek

We are running a daily batch and see sometimes factor 20 runtime diffenencies. Analyzing a trace which recorded fast and slow performance timeframes I isolated a select statement returning a single row from a clustered index which logs a duration of 1101 micos (3 logical Reads) in the “fast” timeframe. A few minutes later the […]

SQL optimal query

i have the following database structure: And i have the task: select the buyer’s name, Id of Order and book’s name for orders that contains less than 3 books. I solved this task so: SELECT b.Name, O.OrderId, bk.Name FROM Orders O JOIN Buyers b ON b.Id = O.BuyerId JOIN BooksInOrder bo ON bo.OrderId = O.OrderId […]

update sql server rows, while reading the same table

I have a database in SQL Server 2012 and want to update a table in it. My table has three columns, the first column is of type nchar(24). It is filled with billion of rows. The other two columns are from the same type, but they are null (empty) at this moment. I need to […]

Is it a good strategy to add a lot of filtered indexes?

I have the following situation: There is a service that any given time can call the same SP in parallel 1 up to 40 – 50 times in the same time. When called ones the SP performs fine (800ms to 1s). As the number of calls grow the times grow. The stored procedure runs on […]

Performance of Delete then Insert vs Merge in SQL Server

I have a SQL Server 2008 query which looks like this Delete from Table1 INSERT INTO TABLE1 SELECT * FROM Table2 And I would like to improve the performance of this query by some means. So, would be a nice approach to replace the Delete and Insert query with Merge statement which handles Delete, Update […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.