March 16, 2009 at 6:16 am
Hi
if i chek sqlservr.exe is taking 1.2 GB of memory. Every time I have to stop the sqlserver and start to increase the performance of my machine
Can any body explain what to do to increase the performence of sqlserver
March 16, 2009 at 6:59 am
is that sql server only used by you or may be multiple user use that...or any application....you have to check first those things ...
Raj Acharya
March 16, 2009 at 7:12 am
Wat is the min memory u have specified?
March 16, 2009 at 7:27 am
here we r using sqlserver2000 and
sql server only used by single user only and
the c:drive is 10 gb
and for qlserver we set 2gb max
plase reply
March 16, 2009 at 8:02 am
set min and max memory to your preferebale choice like 500 mb or whatever you like ...
set min and max memory to one fixed value...like if you set 500 mb than it will take by default 500 mb not more than that....if you have set dynamically memory then sql will take as per it's requirement....so if you want to limit your memory usage you can do like this...
Raj Acharya
March 16, 2009 at 8:12 am
1.2GB is quite a small amount of memory.
How much memory do you have on your server in total?
Is SQL Server the only process running on your server?
How big is your database?
Visit the following link for resources detailing how to configure SQL Server in general, as well as specific memory considerations.
Cheers,
March 16, 2009 at 9:31 am
That's a huge topic. First, you need to figure out what is causing the performance to slow down. Since, at this point, you're completely in the dark, I'd suggest taking a look at this document from Microsoft. It focuses on using the waits and queues to determine where things are slowing down. Once you know what's causing the slow down, then you can start working on fixing it. With no knowledge of what's causing the slow down, any advice now, while well intended, might not help at all.
You might want to look at picking up a book or two and looking through the books online to start getting a handle on what you should be monitoring & looking at.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2009 at 9:43 am
In addition to what Grant has said, you may find it useful to install the Performance Dashboard Reports.
These are great for not only identifying your highest WAIT types but also your slowest queries (by CPU or I/O), any missing indexes you may have etc.
You can download the reports here:
March 16, 2009 at 10:47 am
Thanks for the link to the custom performance reports, being a new DBA and sometimes overwhelmed with all the MS SQL 2005 technologies I think that those reports will help a lot tracking DB slowness issues.
March 16, 2009 at 11:31 am
You're welcome! The Performance Dashboard reports are great, we use them all the time.
If you are pressed for time, why spend 5 minutes writting a query to look at a single aspect of your servers performance, when you can look at everything in 10 seconds by running a report.
That said, do put in the time to learn how to get this information by hand as you will develop a better understanding of what is going on under the hood of SQL Server.
Cheers,
March 16, 2009 at 11:35 am
Should I be worried about any overhead or temp storage that these reports are creating?
I am looking to run these on my production server.
March 16, 2009 at 12:07 pm
You should see no issues whatsover however, if you have a DEV or UAT server at your disposal then I would suggest following the install procedures and testing there first, so you get to know what you are working with.
Most of the data used in the reports is already available on your server in the form of DMV's so the overhead, if any, will come from the execution of the stored procedures.
The report should take seconds to complete but if you are concerned that it is consuming too much resource it is easy enough to just just close the report window to cancel the execution.
March 16, 2009 at 12:10 pm
One thing to note, when you first run the reports you may recieve the following error:
“the difference of two datetime columns caused overflow at runtime”
If you do, I have a fix for this available on my blog along with instructions, which can be found here:
http://johnsansom.com/index.php/2009/01/performance-dashboard-report-datatype-overflow-error/[/url]
Cheers,
March 16, 2009 at 12:10 pm
Don't mean to hyjack this thread, but when running the report on our production server (64bit win2003) I keep getting an error message:
Difference of two datetime columns caused overflow at runtime.
I followed the same process I did when installing the reports on my local PC which was a testing SQL server db installed and I had no issues, however on our production i got that error.
Any idea?
I tried to remove the schema the SQL creates, and re-running the setup.sql with no luck.
March 16, 2009 at 1:28 pm
mirde (3/16/2009)
Don't mean to hyjack this thread, but when running the report on our production server (64bit win2003) I keep getting an error message:Difference of two datetime columns caused overflow at runtime.
I followed the same process I did when installing the reports on my local PC which was a testing SQL server db installed and I had no issues, however on our production i got that error.
Any idea?
I tried to remove the schema the SQL creates, and re-running the setup.sql with no luck.
Hi, the solution to this issue is the post right before yours 😀
Cheers,
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply