Showing posts with label reading. Show all posts
Showing posts with label reading. Show all posts

Wednesday, March 21, 2012

performance comparision of different connection types

Do you know some performance differences between reading data from a stored procedure using:

1. OLE DB Connection and OLE DB Source

2. ADO.NET Connection and DatareaderSource

Przemo

If you could do some testing and share it here I'm sure people would be intreested in reading it.

-Jamie

Monday, March 12, 2012

Performance and filegrowth

I'm new to SQL Server 2000 so I was hoping to get advice from some of you experienced SQL Administrators.

I have been reading about managing user database size and thought that I may be able to gain some performance from my server by changing my current server from automatically growing. i understand that the system must continually monitor the size of each file to determine is and when it needs to grow.

I wanted to change the setting on my server under the options for:

Space allocated for the DATA and Transaction Log files.
Change File Growth settings
Change Maximum File Size settings

My question is - Can this be done while users are connected or should it be done after hours?

Any other information that maybe helpful to me when working with the setting mentioned above would be appreciated. Again I'm an SQL newbie.Those values are dynamic, meaning that they can be implemented while there are active connections to the database. However, if you attempt to increase preallocated space it may impact current users under certain conditions. If performance is that import and there is high activity on the database that affects its growth (or rather requires periodic growth) I'd invest some time to investigate the rate of growth and preallocate enough room so that this background process does not divert server resources during business hours. But keep in mind that this approach will force you to commit more time administering this database, because along with the above you'll have to periodically monitor used space to confirm that the results of your investigation are correct and nothing unusual happened to affect the growth of the space used. You'll also have to perform scheduled increase in preallocated space manually, and preferrably after hours.|||Hello Arra2

I didt agree that you will have increment in performance disabling automatic grow.

In fact if dont realy knows how your database grows, keep it as built
or you will receive SQL error at front end telling that log is full.

If you realy want increase performance use Profile to find what commands are using more CPU (CPU > 2000) or reads (reads > 30000)
or duration (Duration > 4000). Analyse indexes e sql commands structures.

Good Luck

Robinson|||Disabling Auto Growth in high activity environment WILL positively affect the performance, - that's a fact. And I think Mr. Robinson is also confusing Auto Growth with Recovery Mode, which are not the same ;) All other suggestions are actually good, so go get'em, tiger ;)|||Thanks to all for the help.