I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file ?
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanks
It all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:
>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the log
>file ?
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>
|||Thanks Mike
no this was just a sample, just to illustrate the question.
thanks for answering . regards
*** Sent via Developersdex http://www.codecomments.com ***
Showing posts with label expand. Show all posts
Showing posts with label expand. Show all posts
Friday, March 23, 2012
out to expand tempdb log file
I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanksIt all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:
>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the lo
g
>file '
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>|||Thanks Mike
no this was just a sample, just to illustrate the question.
thanks for answering . regards
*** Sent via Developersdex http://www.codecomments.com ***
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanksIt all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:
>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the lo
g
>file '
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>|||Thanks Mike
no this was just a sample, just to illustrate the question.
thanks for answering . regards
*** Sent via Developersdex http://www.codecomments.com ***
out to expand tempdb log file
I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanksThis is a multi-part message in MIME format.
--020102060703060100090302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
It all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:
>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the log
>file '
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>
--020102060703060100090302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It all depends on the amount of "temp" work your server does
(joins, temp tables, temp variables, etc.). Although a tempdb log that
starts at 5MB and can autogrow by 1MB to a max size of 10MB is the
smallest tempdb log file I've ever seen. My small-to-medium sized
production servers have tempdb log files that range from 100MB to 2GB.<br>
<br>
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for
space?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
David Tucker wrote:
<blockquote cite="midOS3S0DhdFHA.1504@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanks
</pre>
</blockquote>
</body>
</html>
--020102060703060100090302--
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanksThis is a multi-part message in MIME format.
--020102060703060100090302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
It all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:
>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the log
>file '
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>
--020102060703060100090302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It all depends on the amount of "temp" work your server does
(joins, temp tables, temp variables, etc.). Although a tempdb log that
starts at 5MB and can autogrow by 1MB to a max size of 10MB is the
smallest tempdb log file I've ever seen. My small-to-medium sized
production servers have tempdb log files that range from 100MB to 2GB.<br>
<br>
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for
space?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
David Tucker wrote:
<blockquote cite="midOS3S0DhdFHA.1504@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanks
</pre>
</blockquote>
</body>
</html>
--020102060703060100090302--
Wednesday, March 21, 2012
Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab
Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
I would first analyze the sql server for bottlenecks. Most of the problems
can be attributed to bad design, poor i/o design , indexing. Have all these
been checked?
Jim
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%235$vpBwyHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
I would first analyze the sql server for bottlenecks. Most of the problems
can be attributed to bad design, poor i/o design , indexing. Have all these
been checked?
Jim
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%235$vpBwyHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>
Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab
Daniel,
This is a question for the .clustering group, so I added it to the list of
groups. Hope you get answer there.
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:OJBAfBwyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>
You can do a node-swap, where you replace each node with a more powerful
one. However, I find chasing performance with hardware is a losing battle.
Find out what resource is maxed out and why. Fix the underlying problem by
rewriting or re-architecting the resource hogs. I usually start a project
like this with a complete assessment and analysis of the existing system so
I can identify likely fixes and present a prioritized list to the client.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23f7V971yHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Daniel,
> This is a question for the .clustering group, so I added it to the list of
> groups. Hope you get answer there.
> --
> Dejan Sarka
> http://blogs.solidq.com/EN/dsarka/
> "Daniel" <softwareengineer98037@.yahoo.com> wrote in message
> news:OJBAfBwyHHA.3848@.TK2MSFTNGP03.phx.gbl...
>
sql
This is a question for the .clustering group, so I added it to the list of
groups. Hope you get answer there.
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:OJBAfBwyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>
You can do a node-swap, where you replace each node with a more powerful
one. However, I find chasing performance with hardware is a losing battle.
Find out what resource is maxed out and why. Fix the underlying problem by
rewriting or re-architecting the resource hogs. I usually start a project
like this with a complete assessment and analysis of the existing system so
I can identify likely fixes and present a prioritized list to the client.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23f7V971yHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Daniel,
> This is a question for the .clustering group, so I added it to the list of
> groups. Hope you get answer there.
> --
> Dejan Sarka
> http://blogs.solidq.com/EN/dsarka/
> "Daniel" <softwareengineer98037@.yahoo.com> wrote in message
> news:OJBAfBwyHHA.3848@.TK2MSFTNGP03.phx.gbl...
>
sql
Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab
Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?Can you define "maxed out"? And what are the actual symptoms?
--
Aaron Bertrand
SQL Server MVP
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:eHiAoBwyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>|||On Fri, 20 Jul 2007 12:01:39 -0700, "Daniel"
<softwareengineer98037@.yahoo.com> wrote:
>Our high end SQL server cluster is maxed out, how else to expand? Will it
>help to move tables off of the cluster onto other clusters or will that just
>create processing bottleneck on the cluster running SQL server?
Please don't post the same question to multiple groups. If you must
send one to two groups, but BOTH groups on the SAME copy so that
replies are seen in both places. (It helps if you use the Subject for
a short Subject too.)
I'm no cluster expert, but when the experts get here they will have
some questions. You might want to have answers ready.
What is maxing out? Disk space? CPU utilization? Memory? The
network?
What does high-end mean in this instance? How many servers, how many
and what type processors, how muchy memory, what sort of disk system?
What is the structure of the cluster? How many machines, running what
operating system/version, running what SQL Server
version(s)/instances? What is the failover arrangement?
Generally the smallest unit of SQL Server that can be moved without
re-writing something is a database. Tables fall within databases. A
question about moving tables sounds like you are a system
administrator but not a DBA. With a high-end cluster that is maxing
out you need the hands-on attention of a DBA.
Roy Harvey
Beacon Falls, CT
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?Can you define "maxed out"? And what are the actual symptoms?
--
Aaron Bertrand
SQL Server MVP
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:eHiAoBwyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>|||On Fri, 20 Jul 2007 12:01:39 -0700, "Daniel"
<softwareengineer98037@.yahoo.com> wrote:
>Our high end SQL server cluster is maxed out, how else to expand? Will it
>help to move tables off of the cluster onto other clusters or will that just
>create processing bottleneck on the cluster running SQL server?
Please don't post the same question to multiple groups. If you must
send one to two groups, but BOTH groups on the SAME copy so that
replies are seen in both places. (It helps if you use the Subject for
a short Subject too.)
I'm no cluster expert, but when the experts get here they will have
some questions. You might want to have answers ready.
What is maxing out? Disk space? CPU utilization? Memory? The
network?
What does high-end mean in this instance? How many servers, how many
and what type processors, how muchy memory, what sort of disk system?
What is the structure of the cluster? How many machines, running what
operating system/version, running what SQL Server
version(s)/instances? What is the failover arrangement?
Generally the smallest unit of SQL Server that can be moved without
re-writing something is a database. Tables fall within databases. A
question about moving tables sounds like you are a system
administrator but not a DBA. With a high-end cluster that is maxing
out you need the hands-on attention of a DBA.
Roy Harvey
Beacon Falls, CT
Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab
Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
Please provide additional details about your SQL cluster (hardware, SQL
server version, configuration, etc.) and what constitutes "maxed out". Where
is the bottleneck -- memory, disk, cpu, network? Performance monitor can
help narrow this down.
Also, moving tables off of one cluster to another may make sense under
certain circumstances but it requires knowing the data and how it is accessed
in the very least to be able to give any informative advice. SQL Server 2005
provides some nice functionality along this line.
"Daniel" wrote:
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that just
> create processing bottleneck on the cluster running SQL server?
>
>
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
Please provide additional details about your SQL cluster (hardware, SQL
server version, configuration, etc.) and what constitutes "maxed out". Where
is the bottleneck -- memory, disk, cpu, network? Performance monitor can
help narrow this down.
Also, moving tables off of one cluster to another may make sense under
certain circumstances but it requires knowing the data and how it is accessed
in the very least to be able to give any informative advice. SQL Server 2005
provides some nice functionality along this line.
"Daniel" wrote:
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that just
> create processing bottleneck on the cluster running SQL server?
>
>
Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab
Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
I'll have to agree. When the speakers sit around in the lounge at a big
conference we trade war stories about clients that have had literally tons
of hardware made unnecessary by some judicious tuning of the database or the
queries. In one case the customer (a BIG company) had not bothered to add
indexes to the tables. They were able to eliminate over half of their
servers. They saved a bunch of money on electricity and the salary of the
kid that designed the database.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"JPD" <nospam@.nospamplease.com> wrote in message
news:Ovf3gapzHHA.3536@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Hi Daniel,
> What do you mean by maxed out. Is it "maxed out" on I/O, memory, CPU? Run
> a trace in Performance Monitor using the following counters and once
> you've identified the bottlenecks act accordingly, i.e. add more memory or
> more/faster disks or RAID, fix application problems (if you're getting
> blocking situations), remove other apps on the server etc. etc. But before
> you take action you need inform yourself on what the problem really is.
>
> a. LogicalDisk (All Counters and All Instances)
> b. Memory (All Counters)
> c. Paging File (All Counters and All Instances)
> d. PhysicalDisk (All Counters and All Instances)
> e. Processor (All Counters and All Instances)
> f. SQLServer:Access Methods (All Counters)
> g. SQLServer:Buffer Manager (All Counters)
> h. SQLServer:Buffer Node (All Counters and All Instances)
> i. SQLServer:Buffer Partition (All Counters and All Instances)
> j. SQLServer:Databases (All Counters and All Instances)
> k. SQLServer:General Statistics (All Counters)
> l. SQLServer:General Latches (All Counters)
> m. SQLServer:General Locks (All Counters and All Instances)
> n. SQLServer:General Memory Manager (All Counters)
> o. SQLServer:General Plan Cache (All Counters and All Instances)
> p. SQLServer:General Transactions (All Counters)
> q. SQLServer:General Wait Statistics (All Counters and All Instances)
> r. Thread (All Counters and All Instances)
>
> Jonathan
>
> Daniel wrote:
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
I'll have to agree. When the speakers sit around in the lounge at a big
conference we trade war stories about clients that have had literally tons
of hardware made unnecessary by some judicious tuning of the database or the
queries. In one case the customer (a BIG company) had not bothered to add
indexes to the tables. They were able to eliminate over half of their
servers. They saved a bunch of money on electricity and the salary of the
kid that designed the database.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"JPD" <nospam@.nospamplease.com> wrote in message
news:Ovf3gapzHHA.3536@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Hi Daniel,
> What do you mean by maxed out. Is it "maxed out" on I/O, memory, CPU? Run
> a trace in Performance Monitor using the following counters and once
> you've identified the bottlenecks act accordingly, i.e. add more memory or
> more/faster disks or RAID, fix application problems (if you're getting
> blocking situations), remove other apps on the server etc. etc. But before
> you take action you need inform yourself on what the problem really is.
>
> a. LogicalDisk (All Counters and All Instances)
> b. Memory (All Counters)
> c. Paging File (All Counters and All Instances)
> d. PhysicalDisk (All Counters and All Instances)
> e. Processor (All Counters and All Instances)
> f. SQLServer:Access Methods (All Counters)
> g. SQLServer:Buffer Manager (All Counters)
> h. SQLServer:Buffer Node (All Counters and All Instances)
> i. SQLServer:Buffer Partition (All Counters and All Instances)
> j. SQLServer:Databases (All Counters and All Instances)
> k. SQLServer:General Statistics (All Counters)
> l. SQLServer:General Latches (All Counters)
> m. SQLServer:General Locks (All Counters and All Instances)
> n. SQLServer:General Memory Manager (All Counters)
> o. SQLServer:General Plan Cache (All Counters and All Instances)
> p. SQLServer:General Transactions (All Counters)
> q. SQLServer:General Wait Statistics (All Counters and All Instances)
> r. Thread (All Counters and All Instances)
>
> Jonathan
>
> Daniel wrote:
Tuesday, March 20, 2012
Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab
Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
Can you define "maxed out"? And what are the actual symptoms?
Aaron Bertrand
SQL Server MVP
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:eHiAoBwyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>
|||On Fri, 20 Jul 2007 12:01:39 -0700, "Daniel"
<softwareengineer98037@.yahoo.com> wrote:
>Our high end SQL server cluster is maxed out, how else to expand? Will it
>help to move tables off of the cluster onto other clusters or will that just
>create processing bottleneck on the cluster running SQL server?
Please don't post the same question to multiple groups. If you must
send one to two groups, but BOTH groups on the SAME copy so that
replies are seen in both places. (It helps if you use the Subject for
a short Subject too.)
I'm no cluster expert, but when the experts get here they will have
some questions. You might want to have answers ready.
What is maxing out? Disk space? CPU utilization? Memory? The
network?
What does high-end mean in this instance? How many servers, how many
and what type processors, how muchy memory, what sort of disk system?
What is the structure of the cluster? How many machines, running what
operating system/version, running what SQL Server
version(s)/instances? What is the failover arrangement?
Generally the smallest unit of SQL Server that can be moved without
re-writing something is a database. Tables fall within databases. A
question about moving tables sounds like you are a system
administrator but not a DBA. With a high-end cluster that is maxing
out you need the hands-on attention of a DBA.
Roy Harvey
Beacon Falls, CT
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
Can you define "maxed out"? And what are the actual symptoms?
Aaron Bertrand
SQL Server MVP
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:eHiAoBwyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>
|||On Fri, 20 Jul 2007 12:01:39 -0700, "Daniel"
<softwareengineer98037@.yahoo.com> wrote:
>Our high end SQL server cluster is maxed out, how else to expand? Will it
>help to move tables off of the cluster onto other clusters or will that just
>create processing bottleneck on the cluster running SQL server?
Please don't post the same question to multiple groups. If you must
send one to two groups, but BOTH groups on the SAME copy so that
replies are seen in both places. (It helps if you use the Subject for
a short Subject too.)
I'm no cluster expert, but when the experts get here they will have
some questions. You might want to have answers ready.
What is maxing out? Disk space? CPU utilization? Memory? The
network?
What does high-end mean in this instance? How many servers, how many
and what type processors, how muchy memory, what sort of disk system?
What is the structure of the cluster? How many machines, running what
operating system/version, running what SQL Server
version(s)/instances? What is the failover arrangement?
Generally the smallest unit of SQL Server that can be moved without
re-writing something is a database. Tables fall within databases. A
question about moving tables sounds like you are a system
administrator but not a DBA. With a high-end cluster that is maxing
out you need the hands-on attention of a DBA.
Roy Harvey
Beacon Falls, CT
Subscribe to:
Posts (Atom)