SP excuting very slow

Question

Hi ,

I have a Sp which is generating Dynamic select query, that query returning some 75000 records. When I am runing this sp Its getting very slow after few minutes I am getting result like "

Msg 1105, Level 17, State 2, Procedure Usp_Dir_UserReportUsersLog, Line 83

Could not allocate space for object 'dbo.Large Object Storage System object:  422589207871488' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

"

Case -2 : I print that select statement and run directly that query  I got result and also its taking only 11 sec.

Can  you please help me to know where I am missing.

Thanks !

---

Regards Vikas Pathak

Answer 1

Sounds like you need to allocate more space to your tempdb and/or turn on auto-growth on tempdb.  If you enable auto-growth on the tempdb, be sure to set to a MB and not percentage growth...and make it a sizable chunk.  You don't want your tempdb (or any database) auto-growing frequently.

Answer 2

Could be a parameter-sniffing problem. This article on my web site explains why you might see different results.
http://www.sommarskog.se/query-plan-mysteries.html

If you want more direct help, please post the procedure code as well as the SQL you actually run.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

The message indicates that SQL Server needed to expand the tempdb database, but was unable to, because your disk was full.

In your stored procedure, you are probably using a lot of temp table or table variables.

If you cannot avoid temporary tables or table variables, and there is no way to let tempdb grow to the size needed, then you could consider creating regular tables instead of temporary tables / table variables.

---

Gert-Jan