TempDB issue

[cryan]

Hello,

I have database that is using high tempdb usage in MSSQL 2008. I found the query that is causing the issue and tried to streamline the query but it still has queries going into the tempdb and uses 100% of HDD which creates lag. The database has high usuage and is around 10million rows.

I have tried to create multiple tempdb files but those just increase the tempdb writing more.

Any suggestions on how to fix the usuage on the tempdb?

Thanks in advance.

[Rabbit]

If the query is causing the problem, then we need to see the query.

[cryan]

;WITH cte_query AS (
  SELECT V.*, 
       ROW_NUMBER() OVER(ORDER BY VR.rank ASC) AS [row_number]
  FROM [dbo].[item_search_rank] AS VR
  INNER JOIN [dbo].[item_category] as A on A.id=VR.id AND A.category_id = @category_id 
  INNER JOIN [dbo].[item_channel] as B on B.id=VR.id AND B.channel_id = @channel_id
  INNER JOIN [dbo].[vn] as V on V.id=VR.id AND (V.language_id=COALESCE(@language_id, V.language_id) or V.language_id is null)
  WHERE VR.enabled = 1
)


SELECT V.*
  VX.channel_xml AS channels
FROM cte_query AS V 
LEFT JOIN [item_channel_xml] AS VX ON VX.id = V.id
WHERE V.[row_number] BETWEEN  @start_row AND @end_row ORDER BY V.[row_number] ASC

The query is on about 10mil rows [vn], 5k rows [channel], 500 rows [category], 10mil rows [item_search_ran k].

[Rabbit]

Please use code tags when posting code.

There's not a whole lot that you'll be able to change to make that query run faster. Do you have the proper indexes on the tables?

One thing that could be slowing it down is the language filter. But that's really a guess, it wouldn't hurt to do a test with it on and with it off.

Instead of a row number query, you could also try a top x query joined to a top x query and see if that runs any faster for you.