We use an ASP/MS SQL 2000 system to send out our mass e-mailing to about 3,500 subscribers (and the list is growing). There are some duplicate entries in the DB and I was thinking about using this code
SELECT DISTINCT email FROM Subscribers
to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).
I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.
What do you performance gurus think?Hi
Distinct does have an overhead but against 3500 records it should go unnoticed I would expect. Suck it an see - test both queries and see how they hold up.
If you are only selecting email and email is indexed (nonclustered) then you have a covered query (the data comes from the index). These values are not duplicated so I would imagine (never tested) that there would be 0 overhead for this. Someone could confirm or correct this. Either way - the overhead should be minimal.
HTH|||Thanks, I also don't think there will be a hit on this small a recordset, but I wanted to ask. As for indexing and other modifications... not today. I need a 30-second solution to this problem created in someone else's old code. These issues will be addressed in our next upgrade, which should be done next month.
Thanks again!|||DISTINCT can have a significant impact on performance, but again this will only be noticable on large datasets.sql
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Friday, March 30, 2012
Tuesday, March 20, 2012
Performance baseline report
Hi all,
I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.
For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.
Anyone know a good refer or test book can help me to create a basline report
Thank a lot!
The following articles should help you going:
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx
http://www.sql-server-performance.com/articles/per/10_baselining_tips_p1.aspx
-Sue
|||Thanks the articles are really helpfull. Do you think the SqL Server Performance Monitoring and Management Tools such as SQL Diagnostic Manager or Spotlight on SQL Server Enterprise are helpful? If I design to buy those tools, do I need to create my own performance baseline report.
|||The decision on purchasing those types of tools often depends on how many servers, databases you need to monitor per DBA and the nature of the databases, how critical they are to the business. Sometimes a "lightweight" shop can get by just buy rolling their own monitoring systems. But if you can get the money for the purchase, it certainly won't hurt! And some of them can really help a more junior level person get up to speed.
Personally, I liked Diagnostic Manager when I used it. I don't think you would really need to baseline separately if using DM. It has a repository to store the data captured and reporting functionality. It has built in reports and you can also write your own. So you can do trend reporting, analysis from a certain point in time and view changes, variances, etc. I know you can download a demo and try it out. Their support was good, company was helpful when evaluating this product - as well as others.
It's been too many years since I used Spotlight and years ago I didn't care for the performance overhead and all the junk in put in the database. I didn't like it, didn't feel it added much value and didn't like cleaning up from the install when we got rid of it. I know they've changed a lot since so I can't really give a decent opinion.
Not exactly the same thing but somewhat along the same lines and as long as we're on the general subject, have you looked at the Performance Dashboard reports from Microsoft? They are more to provide information at a given point in time in terms of performance. But you can export the reports out to files if needed. They are lightweight and do a good job of assessing performance issues, allowing drill through into various areas. You need to be on SQL 2005 SP2 to use them. No other requirements and they are free. Check the following link for a brief overview and a link to the download: http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx
-Sue
Performance baseline report
Hi all,
I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.
For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.
Anyone know a good refer or test book can help me to create a basline report
Thank a lot!
The following articles should help you going:
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx
http://www.sql-server-performance.com/articles/per/10_baselining_tips_p1.aspx
-Sue
|||Thanks the articles are really helpfull. Do you think the SqL Server Performance Monitoring and Management Tools such as SQL Diagnostic Manager or Spotlight on SQL Server Enterprise are helpful? If I design to buy those tools, do I need to create my own performance baseline report.
|||The decision on purchasing those types of tools often depends on how many servers, databases you need to monitor per DBA and the nature of the databases, how critical they are to the business. Sometimes a "lightweight" shop can get by just buy rolling their own monitoring systems. But if you can get the money for the purchase, it certainly won't hurt! And some of them can really help a more junior level person get up to speed.
Personally, I liked Diagnostic Manager when I used it. I don't think you would really need to baseline separately if using DM. It has a repository to store the data captured and reporting functionality. It has built in reports and you can also write your own. So you can do trend reporting, analysis from a certain point in time and view changes, variances, etc. I know you can download a demo and try it out. Their support was good, company was helpful when evaluating this product - as well as others.
It's been too many years since I used Spotlight and years ago I didn't care for the performance overhead and all the junk in put in the database. I didn't like it, didn't feel it added much value and didn't like cleaning up from the install when we got rid of it. I know they've changed a lot since so I can't really give a decent opinion.
Not exactly the same thing but somewhat along the same lines and as long as we're on the general subject, have you looked at the Performance Dashboard reports from Microsoft? They are more to provide information at a given point in time in terms of performance. But you can export the reports out to files if needed. They are lightweight and do a good job of assessing performance issues, allowing drill through into various areas. You need to be on SQL 2005 SP2 to use them. No other requirements and they are free. Check the following link for a brief overview and a link to the download: http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx
-Sue
Performance baseline report
Hi all,
I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.
For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.
Anyone know a good refer or test book can help me to create a basline report
Thank a lot!
The following articles should help you going:
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx
http://www.sql-server-performance.com/articles/per/10_baselining_tips_p1.aspx
-Sue
|||Thanks the articles are really helpfull. Do you think the SqL Server Performance Monitoring and Management Tools such as SQL Diagnostic Manager or Spotlight on SQL Server Enterprise are helpful? If I design to buy those tools, do I need to create my own performance baseline report.
|||The decision on purchasing those types of tools often depends on how many servers, databases you need to monitor per DBA and the nature of the databases, how critical they are to the business. Sometimes a "lightweight" shop can get by just buy rolling their own monitoring systems. But if you can get the money for the purchase, it certainly won't hurt! And some of them can really help a more junior level person get up to speed.
Personally, I liked Diagnostic Manager when I used it. I don't think you would really need to baseline separately if using DM. It has a repository to store the data captured and reporting functionality. It has built in reports and you can also write your own. So you can do trend reporting, analysis from a certain point in time and view changes, variances, etc. I know you can download a demo and try it out. Their support was good, company was helpful when evaluating this product - as well as others.
It's been too many years since I used Spotlight and years ago I didn't care for the performance overhead and all the junk in put in the database. I didn't like it, didn't feel it added much value and didn't like cleaning up from the install when we got rid of it. I know they've changed a lot since so I can't really give a decent opinion.
Not exactly the same thing but somewhat along the same lines and as long as we're on the general subject, have you looked at the Performance Dashboard reports from Microsoft? They are more to provide information at a given point in time in terms of performance. But you can export the reports out to files if needed. They are lightweight and do a good job of assessing performance issues, allowing drill through into various areas. You need to be on SQL 2005 SP2 to use them. No other requirements and they are free. Check the following link for a brief overview and a link to the download: http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx
-Sue
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.c...performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.c...onitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.c...performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.c...onitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Performance and Sizing question
Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.com/articles_performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.com/performance_monitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.com/articles_performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.com/performance_monitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!
Monday, March 12, 2012
performance : load xml file vs query xml field
Does anyone have any benchmarks or general knowledge regarding the performance System.XML 2.0 and SS05 for these two scenarios:
1. XPathDocument xpathdoc = New XPathDocument(pathToXMLFile);
2. storing the contents of the xml file in SS05 ( in a col of XML datatype ) , and using SQLHelper to load it into an xpathdoc
We haven't done such measurements. For case #2, you have a make a roundtrip to the server, which will be more costly than reading from the file, although I cannot say by how much.
I think a fairer comparison is to compare the processing for case #1 with running the query on the XML data type column at the server. XML indexes on the XML column can significantly speed up queries.
Thank you,
Shankar
Friday, March 9, 2012
Performance
One of our system engineers seems to believe that MSSQLServer's performance will be the same if SQLServer was installed on the same drive as the OS. He believes that the entire OS is loaded in memory (lets say 6 gig physical memory) and SQLServer will ut
ilize the entire the drive. BTW: The OS drive is also the location of the Virtual Memory.
Has anyone tested the performance when SQLServer is installed on the OS Drive compared to that if it installed on its own drive.
It will make no difference; the executable itself will be loaded into
memory. What WILL make a difference is placement of data and log files on
different devices. Please refer to "Inside SQL Server" for a good
discussion of this topic.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> One of our system engineers seems to believe that MSSQLServer's
performance will be the same if SQLServer was installed on the same drive as
the OS. He believes that the entire OS is loaded in memory (lets say 6 gig
physical memory) and SQLServer will utilize the entire the drive. BTW: The
OS drive is also the location of the Virtual Memory.
> Has anyone tested the performance when SQLServer is installed on the OS
Drive compared to that if it installed on its own drive.
|||Where would I find this reference material?
-- Adam Machanic wrote: --
It will make no difference; the executable itself will be loaded into
memory. What WILL make a difference is placement of data and log files on
different devices. Please refer to "Inside SQL Server" for a good
discussion of this topic.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> One of our system engineers seems to believe that MSSQLServer's
performance will be the same if SQLServer was installed on the same drive as
the OS. He believes that the entire OS is loaded in memory (lets say 6 gig
physical memory) and SQLServer will utilize the entire the drive. BTW: The
OS drive is also the location of the Virtual Memory.[vbcol=seagreen]
Drive compared to that if it installed on its own drive.
|||It's a book, written by Kalen Delaney. So any good bookstore. I apologize
for not being more specific in my original post.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:B8166487-ED70-47FA-A3F3-D1A50ECB014D@.microsoft.com...
> Where would I find this reference material?
> -- Adam Machanic wrote: --
> It will make no difference; the executable itself will be loaded into
> memory. What WILL make a difference is placement of data and log
files on
> different devices. Please refer to "Inside SQL Server" for a good
> discussion of this topic.
>
> "molonede" <anonymous@.discussions.microsoft.com> wrote in message
> news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> performance will be the same if SQLServer was installed on the same
drive as
> the OS. He believes that the entire OS is loaded in memory (lets say
6 gig
> physical memory) and SQLServer will utilize the entire the drive.
BTW: The[vbcol=seagreen]
> OS drive is also the location of the Virtual Memory.
the OS
> Drive compared to that if it installed on its own drive.
>
>
|||There's a book called Inside SQL Server 2000, so I imagine that's what's
being referred to here.
This is a little bit out of date since it was written for SQL 7 instead of
SQL 2000, but this is one of the better articles on sql perf that I've found
on the web:
http://msdn.microsoft.com/library/de...l7perftune.asp
- Dave
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:B8166487-ED70-47FA-A3F3-D1A50ECB014D@.microsoft.com...
> Where would I find this reference material?
> -- Adam Machanic wrote: --
> It will make no difference; the executable itself will be loaded into
> memory. What WILL make a difference is placement of data and log
files on
> different devices. Please refer to "Inside SQL Server" for a good
> discussion of this topic.
>
> "molonede" <anonymous@.discussions.microsoft.com> wrote in message
> news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> performance will be the same if SQLServer was installed on the same
drive as
> the OS. He believes that the entire OS is loaded in memory (lets say
6 gig
> physical memory) and SQLServer will utilize the entire the drive.
BTW: The[vbcol=seagreen]
> OS drive is also the location of the Virtual Memory.
the OS
> Drive compared to that if it installed on its own drive.
>
>
ilize the entire the drive. BTW: The OS drive is also the location of the Virtual Memory.
Has anyone tested the performance when SQLServer is installed on the OS Drive compared to that if it installed on its own drive.
It will make no difference; the executable itself will be loaded into
memory. What WILL make a difference is placement of data and log files on
different devices. Please refer to "Inside SQL Server" for a good
discussion of this topic.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> One of our system engineers seems to believe that MSSQLServer's
performance will be the same if SQLServer was installed on the same drive as
the OS. He believes that the entire OS is loaded in memory (lets say 6 gig
physical memory) and SQLServer will utilize the entire the drive. BTW: The
OS drive is also the location of the Virtual Memory.
> Has anyone tested the performance when SQLServer is installed on the OS
Drive compared to that if it installed on its own drive.
|||Where would I find this reference material?
-- Adam Machanic wrote: --
It will make no difference; the executable itself will be loaded into
memory. What WILL make a difference is placement of data and log files on
different devices. Please refer to "Inside SQL Server" for a good
discussion of this topic.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> One of our system engineers seems to believe that MSSQLServer's
performance will be the same if SQLServer was installed on the same drive as
the OS. He believes that the entire OS is loaded in memory (lets say 6 gig
physical memory) and SQLServer will utilize the entire the drive. BTW: The
OS drive is also the location of the Virtual Memory.[vbcol=seagreen]
Drive compared to that if it installed on its own drive.
|||It's a book, written by Kalen Delaney. So any good bookstore. I apologize
for not being more specific in my original post.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:B8166487-ED70-47FA-A3F3-D1A50ECB014D@.microsoft.com...
> Where would I find this reference material?
> -- Adam Machanic wrote: --
> It will make no difference; the executable itself will be loaded into
> memory. What WILL make a difference is placement of data and log
files on
> different devices. Please refer to "Inside SQL Server" for a good
> discussion of this topic.
>
> "molonede" <anonymous@.discussions.microsoft.com> wrote in message
> news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> performance will be the same if SQLServer was installed on the same
drive as
> the OS. He believes that the entire OS is loaded in memory (lets say
6 gig
> physical memory) and SQLServer will utilize the entire the drive.
BTW: The[vbcol=seagreen]
> OS drive is also the location of the Virtual Memory.
the OS
> Drive compared to that if it installed on its own drive.
>
>
|||There's a book called Inside SQL Server 2000, so I imagine that's what's
being referred to here.
This is a little bit out of date since it was written for SQL 7 instead of
SQL 2000, but this is one of the better articles on sql perf that I've found
on the web:
http://msdn.microsoft.com/library/de...l7perftune.asp
- Dave
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:B8166487-ED70-47FA-A3F3-D1A50ECB014D@.microsoft.com...
> Where would I find this reference material?
> -- Adam Machanic wrote: --
> It will make no difference; the executable itself will be loaded into
> memory. What WILL make a difference is placement of data and log
files on
> different devices. Please refer to "Inside SQL Server" for a good
> discussion of this topic.
>
> "molonede" <anonymous@.discussions.microsoft.com> wrote in message
> news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> performance will be the same if SQLServer was installed on the same
drive as
> the OS. He believes that the entire OS is loaded in memory (lets say
6 gig
> physical memory) and SQLServer will utilize the entire the drive.
BTW: The[vbcol=seagreen]
> OS drive is also the location of the Virtual Memory.
the OS
> Drive compared to that if it installed on its own drive.
>
>
Wednesday, March 7, 2012
Performance
One of our system engineers seems to believe that MSSQLServer's performance
will be the same if SQLServer was installed on the same drive as the OS. He
believes that the entire OS is loaded in memory (lets say 6 gig physical me
mory) and SQLServer will ut
ilize the entire the drive. BTW: The OS drive is also the location of the V
irtual Memory.
Has anyone tested the performance when SQLServer is installed on the OS Driv
e compared to that if it installed on its own drive.It will make no difference; the executable itself will be loaded into
memory. What WILL make a difference is placement of data and log files on
different devices. Please refer to "Inside SQL Server" for a good
discussion of this topic.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> One of our system engineers seems to believe that MSSQLServer's
performance will be the same if SQLServer was installed on the same drive as
the OS. He believes that the entire OS is loaded in memory (lets say 6 gig
physical memory) and SQLServer will utilize the entire the drive. BTW: The
OS drive is also the location of the Virtual Memory.
> Has anyone tested the performance when SQLServer is installed on the OS
Drive compared to that if it installed on its own drive.|||Where would I find this reference material?
-- Adam Machanic wrote: --
It will make no difference; the executable itself will be loaded into
memory. What WILL make a difference is placement of data and log files on
different devices. Please refer to "Inside SQL Server" for a good
discussion of this topic.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> One of our system engineers seems to believe that MSSQLServer's
performance will be the same if SQLServer was installed on the same drive as
the OS. He believes that the entire OS is loaded in memory (lets say 6 gig
physical memory) and SQLServer will utilize the entire the drive. BTW: The
OS drive is also the location of the Virtual Memory.[vbcol=seagreen]
Drive compared to that if it installed on its own drive.|||It's a book, written by Kalen Delaney. So any good bookstore. I apologize
for not being more specific in my original post.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:B8166487-ED70-47FA-A3F3-D1A50ECB014D@.microsoft.com...
> Where would I find this reference material?
> -- Adam Machanic wrote: --
> It will make no difference; the executable itself will be loaded into
> memory. What WILL make a difference is placement of data and log
files on
> different devices. Please refer to "Inside SQL Server" for a good
> discussion of this topic.
>
> "molonede" <anonymous@.discussions.microsoft.com> wrote in message
> news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> performance will be the same if SQLServer was installed on the same
drive as
> the OS. He believes that the entire OS is loaded in memory (lets say
6 gig
> physical memory) and SQLServer will utilize the entire the drive.
BTW: The
> OS drive is also the location of the Virtual Memory.
the OS[vbcol=seagreen]
> Drive compared to that if it installed on its own drive.
>
>|||There's a book called Inside SQL Server 2000, so I imagine that's what's
being referred to here.
This is a little bit out of date since it was written for SQL 7 instead of
SQL 2000, but this is one of the better articles on sql perf that I've found
on the web:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
- Dave
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:B8166487-ED70-47FA-A3F3-D1A50ECB014D@.microsoft.com...
> Where would I find this reference material?
> -- Adam Machanic wrote: --
> It will make no difference; the executable itself will be loaded into
> memory. What WILL make a difference is placement of data and log
files on
> different devices. Please refer to "Inside SQL Server" for a good
> discussion of this topic.
>
> "molonede" <anonymous@.discussions.microsoft.com> wrote in message
> news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> performance will be the same if SQLServer was installed on the same
drive as
> the OS. He believes that the entire OS is loaded in memory (lets say
6 gig
> physical memory) and SQLServer will utilize the entire the drive.
BTW: The
> OS drive is also the location of the Virtual Memory.
the OS[vbcol=seagreen]
> Drive compared to that if it installed on its own drive.
>
>
will be the same if SQLServer was installed on the same drive as the OS. He
believes that the entire OS is loaded in memory (lets say 6 gig physical me
mory) and SQLServer will ut
ilize the entire the drive. BTW: The OS drive is also the location of the V
irtual Memory.
Has anyone tested the performance when SQLServer is installed on the OS Driv
e compared to that if it installed on its own drive.It will make no difference; the executable itself will be loaded into
memory. What WILL make a difference is placement of data and log files on
different devices. Please refer to "Inside SQL Server" for a good
discussion of this topic.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> One of our system engineers seems to believe that MSSQLServer's
performance will be the same if SQLServer was installed on the same drive as
the OS. He believes that the entire OS is loaded in memory (lets say 6 gig
physical memory) and SQLServer will utilize the entire the drive. BTW: The
OS drive is also the location of the Virtual Memory.
> Has anyone tested the performance when SQLServer is installed on the OS
Drive compared to that if it installed on its own drive.|||Where would I find this reference material?
-- Adam Machanic wrote: --
It will make no difference; the executable itself will be loaded into
memory. What WILL make a difference is placement of data and log files on
different devices. Please refer to "Inside SQL Server" for a good
discussion of this topic.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> One of our system engineers seems to believe that MSSQLServer's
performance will be the same if SQLServer was installed on the same drive as
the OS. He believes that the entire OS is loaded in memory (lets say 6 gig
physical memory) and SQLServer will utilize the entire the drive. BTW: The
OS drive is also the location of the Virtual Memory.[vbcol=seagreen]
Drive compared to that if it installed on its own drive.|||It's a book, written by Kalen Delaney. So any good bookstore. I apologize
for not being more specific in my original post.
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:B8166487-ED70-47FA-A3F3-D1A50ECB014D@.microsoft.com...
> Where would I find this reference material?
> -- Adam Machanic wrote: --
> It will make no difference; the executable itself will be loaded into
> memory. What WILL make a difference is placement of data and log
files on
> different devices. Please refer to "Inside SQL Server" for a good
> discussion of this topic.
>
> "molonede" <anonymous@.discussions.microsoft.com> wrote in message
> news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> performance will be the same if SQLServer was installed on the same
drive as
> the OS. He believes that the entire OS is loaded in memory (lets say
6 gig
> physical memory) and SQLServer will utilize the entire the drive.
BTW: The
> OS drive is also the location of the Virtual Memory.
the OS[vbcol=seagreen]
> Drive compared to that if it installed on its own drive.
>
>|||There's a book called Inside SQL Server 2000, so I imagine that's what's
being referred to here.
This is a little bit out of date since it was written for SQL 7 instead of
SQL 2000, but this is one of the better articles on sql perf that I've found
on the web:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
- Dave
"molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:B8166487-ED70-47FA-A3F3-D1A50ECB014D@.microsoft.com...
> Where would I find this reference material?
> -- Adam Machanic wrote: --
> It will make no difference; the executable itself will be loaded into
> memory. What WILL make a difference is placement of data and log
files on
> different devices. Please refer to "Inside SQL Server" for a good
> discussion of this topic.
>
> "molonede" <anonymous@.discussions.microsoft.com> wrote in message
> news:102A14DC-430C-4C7C-A3EC-2FC874DF0FF9@.microsoft.com...
> performance will be the same if SQLServer was installed on the same
drive as
> the OS. He believes that the entire OS is loaded in memory (lets say
6 gig
> physical memory) and SQLServer will utilize the entire the drive.
BTW: The
> OS drive is also the location of the Virtual Memory.
the OS[vbcol=seagreen]
> Drive compared to that if it installed on its own drive.
>
>
Saturday, February 25, 2012
Perfomance tuning
Dear friends
We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000
Thanks in Advance
Filson
A possible course of action is this:
Try to check your IIS's and SQL's processor during the peak times to see if the problem is with your SQL or IIS
If your problem is not in IIS:
Try to see if your sql server's processor is 100% or near. If it is, then your queries are probably too heavy - if it is not, then you possibly have locking problems.
Try to find if there are specific queries that are taking longer. You could use Sql Profiler for this, by specifying a duration minimum threshold for the events - for instance, only show queries that take more than 5 seconds (warning: this might deterioate even further your performance while the trace is active). After finding out the worse queries, try to understand why they are running slow - you could use SQL Query Analyzer for this, using the SP's execution plans.
Subscribe to:
Posts (Atom)