October 24, 2008 at 5:32 pm
Also, running DBCC commands, even (gasp) SHRINKFILE or SHRINKDB can be done ONLINE... it will slow things down a bit but the system will still be available.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2008 at 5:41 pm
Sandy,
Along with the other recommendations for regular DB, index, and other forms of maintenance, if you still are having performance problems after that, there's a pretty good chance that the code on both the GUI and T-SQL sides of the house need to be looked at and, maybe, rewritten.
You can also have some hardware problems with disk I/O and comms between boxes. For example, on many routers and network cards with automatic features, if you allow the setting to be "auto negotiate" between two servers, it will sometimes make the horrible choice of "half duplex" and performance will suffer greatly in most cases.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2008 at 12:17 am
Hi All, Sorry for late reply...
Jeff, Thanks for your advice and The_SQL_DBA. Can you please suggest for some other idea also. I appreciate the idea given by you both really. and Meely (:P) also..
Cheers!
Sandy.
--
October 27, 2008 at 5:52 am
Not without actually putting my hand on the system and not with my hands tied by rules like not being able to change code which is likely the biggest performance problem. Hardware tuning usually accounts for about 5% of most improvements that can be made... the rest is all database design and code.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2008 at 6:12 am
Yeah Jeff,
Thanks .....I will check this..I will try to come up with new idea...
Cheers!
Sandy.
--
October 27, 2008 at 9:34 am
SQL Server 2005 keeps some informative statistics about query execution plans in the dynamic management views. These queries can help you easily identify long running and I/O intensive queries:
-- most time queries
SELECT TOP 100
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;
-- most I/O queries
SELECT TOP 100
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_IO DESC;
Once you know what queries are causing problems, then you can consider if indexing will help or not.
If you think the problem could be blocking you can see what sessions are currently waiting or blocked:
-- currently waiting
select * from sys.dm_os_waiting_tasks
where session_id >= 50
October 27, 2008 at 8:24 pm
Isn't there a canned report you can get for the "top 10" longest queries just by right clicking on the database or server?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2008 at 9:23 am
1) Sandy, you really need to get a perf tuning consultant to mentor you in how to investigate and correct sql server performance tuning issues. There are a kajillion things that can come into play here. I, like others, have a concern for whether or not you are doing other important DBA activities as well that are designed to keep your company's data available, recoverable, etc.
2) To The_SQL_DBA, I think you missed one of the most important issues with shrink: it rearranges the database pages internally, which leads to severe fragmentation INSIDE the sql data file, not just external OS file fragmentation.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 29, 2008 at 6:04 am
Chris, It is a very nice query but when i am trying to executed in SQL 2005 its working fine except the DB which is restored from SQL 2000 DB.
When i am trying to execute this from SQL 2000, Its showing error in "MAX". Can you tell me why?
TheSQLGuru,
1) Sandy, you really need to get a perf tuning consultant to mentor you in how to investigate and correct sql server performance tuning issues. There are a kajillion things that can come into play here. I, like others, have a concern for whether or not you are doing other important DBA activities as well that are designed to keep your company's data available, recoverable, etc.
2) To The_SQL_DBA, I think you missed one of the most important issues with shrink: it rearranges the database pages internally, which leads to severe fragmentation INSIDE the sql data file, not just external OS file fragmentation.
As per the your post, I am not a fully DBA Person, But I have good holds on DBA commands. What I wanted to know from here is apart from DBCC what are the possible command can we apply for the Database performance. and also I wanted to know from you what other activity you are talking about...Can you please clear me..?
I am not able to understand your 2nd point. can you please tell in details?
Cheers!
Sandy.
--
October 29, 2008 at 6:59 am
As per the your post, I am not a fully DBA Person, But I have good holds on DBA commands. What I wanted to know from here is apart from DBCC what are the possible command can we apply for the Database performance. and also I wanted to know from you what other activity you are talking about...Can you please clear me..?
I am not able to understand your 2nd point. can you please tell in details?
Cheers!
Sandy.
I can't clear you up on my first part. A kajillion things means a LOT of them, many of which interplay with each other too. You simply cannot learn how to tune a sql server or databases on it or applications that hit those databases from some forum posts. You need training, mentoring, self-study and EXPERIENCE.
For my second point, I don't really think I CAN give you a better description than I did. 🙂 That is as concise and descriptive as I can make it. The problem is that you are missing a good bit of fundamental knowledge. Go to http://www.mssqltips.com/search.asp and type in fragmentation station as a search criteria and carefully read all of the posts that come back.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 29, 2008 at 7:00 am
Hi Sandy,
Chris's command will only work in SS2K5 and then only for databases that have a compatibility of 90. The database you restored from SS2K will have a compatibility of 80, so that's a no-go.
To understand internal fragmentation you really need to know something about the way SQL Server stores data. Try looking in BOL. Maybe start with reading about pages and extents as they're the basic units of storage.
Cath
October 29, 2008 at 8:04 am
Hi I hope you are running this scripts on a non-bussines hours, you can put the recovery model in simple before you run your task and then after it finished returns the recovery model to full.
October 29, 2008 at 11:02 am
Sandy (10/29/2008)
Chris, It is a very nice query but when i am trying to executed in SQL 2005 its working fine except the DB which is restored from SQL 2000 DB.When i am trying to execute this from SQL 2000, Its showing error in "MAX". Can you tell me why?
TheSQLGuru,
1) Sandy, you really need to get a perf tuning consultant to mentor you in how to investigate and correct sql server performance tuning issues. There are a kajillion things that can come into play here. I, like others, have a concern for whether or not you are doing other important DBA activities as well that are designed to keep your company's data available, recoverable, etc.
2) To The_SQL_DBA, I think you missed one of the most important issues with shrink: it rearranges the database pages internally, which leads to severe fragmentation INSIDE the sql data file, not just external OS file fragmentation.
As per the your post, I am not a fully DBA Person, But I have good holds on DBA commands. What I wanted to know from here is apart from DBCC what are the possible command can we apply for the Database performance. and also I wanted to know from you what other activity you are talking about...Can you please clear me..?
I am not able to understand your 2nd point. can you please tell in details?
Cheers!
Sandy.
Sandy try this:
"DBCC CHECKDB WITH DATA_PURITY" run it in the database that is slow.
If it spits out errors run this
"DBCC UPDATEUSAGE ('slow database','tablename with error from above statement')"
dbname and tbalename must be enclosed in single quotes.
October 29, 2008 at 10:53 pm
I can't clear you up on my first part. A kajillion things means a LOT of them, many of which interplay with each other too. You simply cannot learn how to tune a sql server or databases on it or applications that hit those databases from some forum posts. You need training, mentoring, self-study and EXPERIENCE.
For my second point, I don't really think I CAN give you a better description than I did. That is as concise and descriptive as I can make it. The problem is that you are missing a good bit of fundamental knowledge. Go to http://www.mssqltips.com/search.asp and type in fragmentation station as a search criteria and carefully read all of the posts that come back.
TheSQLGuru,
I do agree with you regarding the point learning the tuning DB from Forums post. yes I need to do some RND on it. But when you are saying this particular word "kajillion things" what do you mean?
There is no such activity present in SQL Server as per my knowledge.
Just brief it so everybody can know what DBA activity you knows more about than others. So I can learn from you at least some commands.
for 2nd point i will check it.
NB: If you are saying something that should reach to others otherwise it does not make sense to post for question.
Cheers!
Sandy.
--
October 30, 2008 at 7:25 am
I became aware reading your various posts that there was a language disconnect here, which is why I made this statement in my follow-up post: " A kajillion things means a LOT of them". I really thought you would know what "a lot" means! How about many, plenty, incredible amount, more than a few, a plethora, mucho, metric butt ton, scads, buckets, oodles, etc. 😀
See here for a more complete discourse on non-specific large numbers in the english vernacular: http://www.reference.com/search?q=Kajillion
And discourse and vernacular can be looked up here: http://www.dictionary.com 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply