Today again, I have a MAJOR issue with what appears to be parameter sniffing in SQL Server 2005.
I have a query comparing some results with known good results. I added a column to the results and the known good results, so that each month, I can load a new months results in both sides and compare only the current month. The new column...
On SQL Server 2005, I have a complex multi-level allocation process which looks like this (pseudo-SQL):
FOR EACH @LVL_NUM < @MAX_LVL:
INSERT INTO ALLOCS
SELECT 'OUT', *
FROM BALANCES(@LVL_NUM)
INNER JOIN ALLOCN_SUMRY(@LVL_NUM)
INSERT INTO ALLOCS
SELECT 'IN', *
FROM BALANCES(@LVL_NUM)
INNER JOIN ALLOCNS(@LVL_NUM)
INNER JOIN ALLOCN_SUMRY...
I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in:
CREATE PROCEDURE MyProc
@MyDate DATETIME = NULL
AS
IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
-- Do Something using @MyDate
I'm having problems whereby if @MyDate is passed in as NULL when the stored ...
I got a complex report using reporting service, the report connect to a SQl 2005 database, and calling a number of store procedure and functions. it works ok initially, but after a few months(data grows), it run into timeout error.
I created a few indexes to improve the performance, but the strange thing it that it works after the inde...
(At first glance this may look like a duplicate of http://stackoverflow.com/questions/421275 or http://stackoverflow.com/questions/414336, but my actual question is a bit different)
Alright, this one's had me stumped for a few hours. My example here is ridiculously abstracted, so I doubt it will be possible to recreate locally, but it p...
Can someone please explain why this works. Here is the scenerio. I have a stored proc and it starts to run slow. Then I pick a parameter and declare a variable to house its value and in the proc use the declared variable instead of the parameter. The proc will then speed up considerably.
I think it has something to do with the cache...