Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2011-03-22 : 14:53:30
|
| Hi,I am trying to get the execution plan for a query that produces the following error. Temp DB's primary file group has 4096 MB allocated to it. It is a pretty long queuery and I would like to see where is the bottle neck is.I am just attaching the query below. I get the error even if I limit the number of orders to 5 insteas of 50,000. So, the bottle neck is somewhere else. I am not sure how to find it without an execution plan. Can any one have a suggestion?Could not allocate space for object '<temporary system object: 422263272636416>' 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.Thanks for your time..Query<code>SELECT TOP 50000 [order_number], performance.performance_description [performance.performance_description], performance.performance_name [performance.performance_name], [miscellaneousitem_name], [miscellaneousitemoption_name], [orderitem_comment], [ordermarketingdata_data1], [ordermarketingdata_data2], [order_due], COUNT(DISTINCT CAST(admission_id AS NVARCHAR(50))) [COUNT_admission_id], COUNT(DISTINCT CAST(orderitem_id AS NVARCHAR(50))) [COUNT_orderitem_id], COUNT(DISTINCT ts_ticket.ticket_number) [COUNT_ts_ticket.ticket_number], SUM(orderadmission_amount) [SUM_orderadmission_amount], SUM(orderitem_amount) [SUM_orderitem_amount] FROM ts_order WITH (NOLOCK) LEFT JOIN ts_customer order_customer with (NOLOCK) on order_customer_id = order_customer.customer_id LEFT JOIN ts_order_admission with (NOLOCK) on orderadmission_order_id = order_id LEFT JOIN ts_performance performance with (NOLOCK) on performance.performance_id = orderadmission_performance_id LEFT JOIN ts_performance_marketing_data with (NOLOCK) on performancemarketingdata_performance_id = performance.performance_id LEFT JOIN ts_series series with (NOLOCK) on performance.performance_series_id = series.series_id LEFT JOIN ts_order_item with (NOLOCK) on orderitem_order_id = order_id LEFT JOIN ts_miscellaneous_item with (NOLOCK) on orderitem_miscellaneous_item_id = miscellaneousitem_id LEFT JOIN ts_miscellaneous_item_option with (NOLOCK) ON orderitem_option_id = miscellaneousitemoption_id LEFT JOIN ts_order_marketing_data with (NOLOCK) on ordermarketingdata_order_id = order_id LEFT JOIN ts_admission with (NOLOCK) on admission_id = orderadmission_admission_id LEFT JOIN ts_ticket with (NOLOCK) on order_id = ts_ticket.ticket_order_id left join ts_audit as ticket_audit with (NOLOCK) on ticket_audit_id = ticket_audit.audit_id left outer join ts_user as ticket_auditb with (NOLOCK) on ticket_audit.uuid = ticket_auditb.user_id left outer join ts_user as ticket_auditc with (NOLOCK) on ticket_audit.override_uuid = ticket_auditc.user_id WHERE ( ( (order_customer.customer_reference_code) = N'2500') ) GROUP BY order_number, performance.performance_description, performance.performance_name, miscellaneousitem_name, miscellaneousitemoption_name, orderitem_comment, ordermarketingdata_data1, ordermarketingdata_data2, order_due ORDER BY [performance.performance_name] DESC, [miscellaneousitem_name] DESC</code> |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-22 : 15:02:37
|
| Why can't you get an execution plan?Have your admin grant you showplan permissions. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-22 : 15:12:51
|
| It seems your tempdb is sized too small for your query. No matter what TOP value you use, it's going to need to create the whole thing first, sort it, and then give you back the number of rows you selected.Execution plan or not, you've got size problem and not a performance problem. Can you limit the query via additional filters in the WHERE clause? Just how big is this thing if you did COUNT(*) instead?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-22 : 15:14:11
|
| Agreed, I was thinking along the line that the OP could at least view estimated exec plan. |
|
|
|
|
|
|
|
|
Can't get an execution Plan