Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #875 > unrolled thread

Increase tempdb database, SQL 2008 R2

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Increase tempdb database, SQL 2008 R2 "m" <web12master@gmail.com> - 2011-12-21 16:26 +0100
    Re: Increase tempdb database, SQL 2008 R2 "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-12-21 11:12 -0500
      Re: Increase tempdb database, SQL 2008 R2 "m" <web12master@gmail.com> - 2011-12-22 08:25 +0100
        Re: Increase tempdb database, SQL 2008 R2 Erland Sommarskog <esquel@sommarskog.se> - 2011-12-22 08:45 +0000
          Re: Increase tempdb database, SQL 2008 R2 "m" <web12master@gmail.com> - 2011-12-22 09:59 +0100
            Re: Increase tempdb database, SQL 2008 R2 "m" <web12master@gmail.com> - 2011-12-22 13:43 +0100
              Re: Increase tempdb database, SQL 2008 R2 Erland Sommarskog <esquel@sommarskog.se> - 2011-12-22 23:28 +0100
                Re: Increase tempdb database, SQL 2008 R2 "m" <web12master@gmail.com> - 2011-12-23 09:23 +0100
              Re: Increase tempdb database, SQL 2008 R2 "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-12-23 09:24 -0500
        Re: Increase tempdb database, SQL 2008 R2 "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-12-22 06:17 -0500
    Re: Increase tempdb database, SQL 2008 R2 Erland Sommarskog <esquel@sommarskog.se> - 2011-12-21 23:36 +0100

#875 — Increase tempdb database, SQL 2008 R2

Hello,

I need to increase tempdb database on SQL Server 2008 R2.

Can I do it in business hours?
Is there any negative inpact on the system?

regards,

m 

[toc] | [next] | [standalone]

#876

m wrote:
> Hello,
>
> I need to increase tempdb database on SQL Server 2008 R2.
>
> Can I do it in business hours?
> Is there any negative inpact on the system?
>
Why? Had you trned autogrow off? I believe you can turn it back on without
restarting.

[toc] | [prev] | [next] | [standalone]

#881

We have bad performanse on that SQL server,
I noticed that tempdb is smaller than it should be (producer of software we 
use on that sql server advice to have inital min 100MB of tempdb)

Now initial size of tempdb is 8MB with 10% of autogrowth and size is 90MB
and application database is about 4GB.

I'm planing to put inital 200MB with autogrowth 25%
What do you think?


"Bob Barrows" <reb01501@NOyahooSPAM.com> wrote in message 
news:jct0hl$dis$1@dont-email.me...
>m wrote:
>> Hello,
>>
>> I need to increase tempdb database on SQL Server 2008 R2.
>>
>> Can I do it in business hours?
>> Is there any negative inpact on the system?
>>
> Why? Had you trned autogrow off? I believe you can turn it back on without
> restarting.
>
> 

[toc] | [prev] | [next] | [standalone]

#882

m (web12master@gmail.com) writes:
> We have bad performanse on that SQL server, I noticed that tempdb is
> smaller than it should be (producer of software we use on that sql
> server advice to have inital min 100MB of tempdb) 
> 
> Now initial size of tempdb is 8MB with 10% of autogrowth and size is 90MB
> and application database is about 4GB.
> 
> I'm planing to put inital 200MB with autogrowth 25%
> What do you think?
 
Even without instant file initialization, clearing 200 MB of disk is not
that painful. Go for it.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#883

Thank you.

Regards

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9FC363373690EYazorman@127.0.0.1...
>m (web12master@gmail.com) writes:
>> We have bad performanse on that SQL server, I noticed that tempdb is
>> smaller than it should be (producer of software we use on that sql
>> server advice to have inital min 100MB of tempdb)
>>
>> Now initial size of tempdb is 8MB with 10% of autogrowth and size is 90MB
>> and application database is about 4GB.
>>
>> I'm planing to put inital 200MB with autogrowth 25%
>> What do you think?
>
> Even without instant file initialization, clearing 200 MB of disk is not
> that painful. Go for it.
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 

[toc] | [prev] | [next] | [standalone]

#885

Thanks.

We have 8GB RAM on that SQL server.

What do you think if we limit SQL memory to 6GB, would there be any 
improvment on server performance?


"m" <web12master@gmail.com> wrote in message 
news:jcuri7$e6j$1@gregory.bnet.hr...
> Thank you.
>
> Regards
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
> news:Xns9FC363373690EYazorman@127.0.0.1...
>>m (web12master@gmail.com) writes:
>>> We have bad performanse on that SQL server, I noticed that tempdb is
>>> smaller than it should be (producer of software we use on that sql
>>> server advice to have inital min 100MB of tempdb)
>>>
>>> Now initial size of tempdb is 8MB with 10% of autogrowth and size is 
>>> 90MB
>>> and application database is about 4GB.
>>>
>>> I'm planing to put inital 200MB with autogrowth 25%
>>> What do you think?
>>
>> Even without instant file initialization, clearing 200 MB of disk is not
>> that painful. Go for it.
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> 

[toc] | [prev] | [next] | [standalone]

#886

m (web12master@gmail.com) writes:
> We have 8GB RAM on that SQL server.
> 
> What do you think if we limit SQL memory to 6GB, would there be any 
> improvment on server performance?
 
Since the database is only 4GB, there should be plenty of headroom.

Is this 32-bit or 64-bit SQL Server?

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#887

This is Microsoft SQL Server Enterprise Edition (64-bit) 2008 R2


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9FC3EED4A976CYazorman@127.0.0.1...
>m (web12master@gmail.com) writes:
>> We have 8GB RAM on that SQL server.
>>
>> What do you think if we limit SQL memory to 6GB, would there be any
>> improvment on server performance?
>
> Since the database is only 4GB, there should be plenty of headroom.
>
> Is this 32-bit or 64-bit SQL Server?
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> 

[toc] | [prev] | [next] | [standalone]

#888

m wrote:
> Thanks.
>
> We have 8GB RAM on that SQL server.
>
> What do you think if we limit SQL memory to 6GB, would there be any
> improvment on server performance?
>

I don't know. There might be ... there might not be. It depends on if memory
issues are causing the performance problems.

It sounds like you're just willy-nilly throwing solutions at the server
hoping one of them will luckily solve your problem. You need to do some
monitoring and tracing to find out where your performance bottlenecks really
are. Here are some tools that can help in that regard:
http://pal.codeplex.com/

Install and run the SQL 2008 R2 Best Practices Analyzer
http://blogs.msdn.com/b/psssql/archive/2010/06/20/introducing-the-sql-server-2008-r2-best-practices-analyzer-bpa.aspx

Look at the event logs and see if any IO or memory pressure situations are
being reported.

Do some index analysis and see if there are indexes that can be created that
will help:
http://blogs.msdn.com/b/psssql/archive/2010/06/20/introducing-the-sql-server-2008-r2-best-practices-analyzer-bpa.aspx

or indexes that can be dropped due to lack of use:
https://www.google.com/search?hl=en&safe=active&q=sql+unused+indexes&oq=sql+unused+indexes&aq=f&aqi=g1g-v4g-b5&aql=&gs_sm=e&gs_upl=67889l69117l0l70056l7l7l0l0l0l0l201l798l3.3.1l7l0

Hire a consultant to analyze your system and make recommendations.

[toc] | [prev] | [next] | [standalone]

#884

With Autogrow on, the database is already as big as it needs to be 
(depending on how long it's been since the last restart). The size of your 
application database is not really relevant unless you're doing thins like 
sorting in tempdb. Even still, autogrow has already made tempdb as big as it 
needs to be. Why are you blaming tempdb for your sluggish performance? Are 
you noticing that many waits while tempdb is resized?

That said, however, as Erland put it, 200 Mb is nothing. Go for it. Then 
monitor it for a while to see if it remains at 200 Mb.

m wrote:
> We have bad performanse on that SQL server,
> I noticed that tempdb is smaller than it should be (producer of
> software we use on that sql server advice to have inital min 100MB of
> tempdb)
> Now initial size of tempdb is 8MB with 10% of autogrowth and size is
> 90MB and application database is about 4GB.
>
> I'm planing to put inital 200MB with autogrowth 25%
> What do you think?
>
>
> "Bob Barrows" <reb01501@NOyahooSPAM.com> wrote in message
> news:jct0hl$dis$1@dont-email.me...
>> m wrote:
>>> Hello,
>>>
>>> I need to increase tempdb database on SQL Server 2008 R2.
>>>
>>> Can I do it in business hours?
>>> Is there any negative inpact on the system?
>>>
>> Why? Had you trned autogrow off? I believe you can turn it back on
>> without restarting. 

[toc] | [prev] | [next] | [standalone]

#880

m (web12master@gmail.com) writes:
> I need to increase tempdb database on SQL Server 2008 R2.
> 
> Can I do it in business hours?
> Is there any negative inpact on the system?
 
If the service account does not have the Windows permission Perform Volume 
Maintenance Tasks, SQL Server needs to clear all pages in the newly 
allocated space, and that will take resources.
-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web