Hi , I want to share some strange or may be normal performance issue i faced today .
one of my developers called me today and said F drive is out of Space.Then i went across the windows and sql logs and sys.process, ldf files of all databases and found nothing useful for 1 hour of my time and found tempdb mdf file is 100GB , so hardly i could do was to shrink Tempdb ldf and then mdf which bought me space back ,
then again after my developer ran his insert statement it got filled out again , so i asked his to pass me the insert querey he is running which is doing heavy UNION ALL,HAVING and ORDER BY clauses and extracting 2000 rows only , then i went across Indexes fragmentation , Statistics info and found everything looks ok but still tempdb is 100GB, so i used display estimated execution plan with the querey my developer is using and it gave me info to add a non clustered index on particular columns , then i created an non-clustered index , and shrink the tempdb mdf and ldf , then the space came back to 100GB free ...
and then i asked my developer to run the querey again , which ran very well and tempdb did not get filled ...
Always use display estimated execution plan for the queries you are about to run , it costs nothing ..
SO Tempdb gets filled for many reasons
Tempdb gets filled very quickly when we run on large result sets including UNIONS, ORDERBY, GROUPBY clauses,
another reason the tempdb can grow is due to a query returning heavy data,
Worktables that hold intermediate results created during query processing and sorting
Materialized static cursors
Thanks
one of my developers called me today and said F drive is out of Space.Then i went across the windows and sql logs and sys.process, ldf files of all databases and found nothing useful for 1 hour of my time and found tempdb mdf file is 100GB , so hardly i could do was to shrink Tempdb ldf and then mdf which bought me space back ,
then again after my developer ran his insert statement it got filled out again , so i asked his to pass me the insert querey he is running which is doing heavy UNION ALL,HAVING and ORDER BY clauses and extracting 2000 rows only , then i went across Indexes fragmentation , Statistics info and found everything looks ok but still tempdb is 100GB, so i used display estimated execution plan with the querey my developer is using and it gave me info to add a non clustered index on particular columns , then i created an non-clustered index , and shrink the tempdb mdf and ldf , then the space came back to 100GB free ...
and then i asked my developer to run the querey again , which ran very well and tempdb did not get filled ...
Always use display estimated execution plan for the queries you are about to run , it costs nothing ..
SO Tempdb gets filled for many reasons
Tempdb gets filled very quickly when we run on large result sets including UNIONS, ORDERBY, GROUPBY clauses,
another reason the tempdb can grow is due to a query returning heavy data,
Worktables that hold intermediate results created during query processing and sorting
Materialized static cursors
Thanks
No comments:
Post a Comment