adhoc queries

Hello

We got third party software, we are using adhoc queries more for the Applications (i am checking the DB Dashboard, it is showing as 97% CPU for adhoc queries). Could any one suggest how to tune SQL Server (at server level) for adhoc queries, we can not change the code now.

Let me know how to improve the SQL Server, whenever 3rd party applications use adhoc queries more?

Thanks in advance.

You can use DTA for this.

http://msdn2.microsoft.com/en-us/library/ms186354.aspx

You will want to use sql profiler to capture a trace/workload for DTA.

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?
Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:

> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> 4 more procs"?
>
>
|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
adding[vbcol=seagreen]
|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:

> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
>
>
|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:

> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to addi
ng
> 4 more procs"?
>
>|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>
adding[vbcol=seagreen]|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask a
nd Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is
AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:

> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask
and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there i
s AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
>
>|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> 4 more procs"?
>
>|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
> > We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> > The machine currently has 4 procs and 32GB physical memory.
> > We will be going to 8 procs and 64GB.
> > I'm pretty sure that the memory add is not big deal.
> >
> > My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
adding
> > 4 more procs"?
> >
> >
> >|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
>> The machine currently has 4 procs and 32GB physical memory.
>> We will be going to 8 procs and 64GB.
>> I'm pretty sure that the memory add is not big deal.
>> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
>> 4 more procs"?
>>|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:
> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> > Hi
> >
> > On startup, when the OS detects the additional CPU's it uses them
> > automatically. The same goes for SQL Server.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "frankm" wrote:
> >
> >> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> >> The machine currently has 4 procs and 32GB physical memory.
> >> We will be going to 8 procs and 64GB.
> >> I'm pretty sure that the memory add is not big deal.
> >>
> >> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> >> 4 more procs"?
> >>
> >>
> >>
>
>|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Adding an additional CPU SQL 2005 Enterprise

Hi,
I have 2 SQL 2005 Enterprise servers currently running one a single CPU
license. I have now purchased an additional enterprise CPU license and would
like to `activate` an additional CPU.
How would I do this?
Many thanks
RichardIf you had more than 1 processor on the machine you were violating the
license agreement to begin with. If you have 2 then you need two licenses
regardless of how many you intended to use. The license does not prohibit or
enable the use of the processors.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Thompson" <sbc@.thompson.co.za> wrote in message
news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
> Hi,
> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
> license. I have now purchased an additional enterprise CPU license and
> would like to `activate` an additional CPU.
> How would I do this?
> Many thanks
> Richard|||Ok, thanks for that. So I now have a 2 CPU license I can just add the second
CPU and it will be fine no need to worry about changing settings in SQL at
all.
Thanks a lot,
Richard
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9Ipsq3$HHA.5652@.TK2MSFTNGP05.phx.gbl...
> If you had more than 1 processor on the machine you were violating the
> license agreement to begin with. If you have 2 then you need two licenses
> regardless of how many you intended to use. The license does not prohibit
> or enable the use of the processors.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Richard Thompson" <sbc@.thompson.co.za> wrote in message
> news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
>> Hi,
>> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
>> license. I have now purchased an additional enterprise CPU license and
>> would like to `activate` an additional CPU.
>> How would I do this?
>> Many thanks
>> Richard
>|||By adding the 2nd CPU do you mean physically installing one? If so then
yes, the OS & SQL Server should detect the 2nd CPU and it will know what to
do without changing any settings unless you already changed some from the
defaults.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Thompson" <sbc@.thompson.co.za> wrote in message
news:31A8B061-7F53-4EB2-AA6A-F96AB2103149@.microsoft.com...
> Ok, thanks for that. So I now have a 2 CPU license I can just add the
> second CPU and it will be fine no need to worry about changing settings in
> SQL at all.
> Thanks a lot,
> Richard
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9Ipsq3$HHA.5652@.TK2MSFTNGP05.phx.gbl...
>> If you had more than 1 processor on the machine you were violating the
>> license agreement to begin with. If you have 2 then you need two licenses
>> regardless of how many you intended to use. The license does not prohibit
>> or enable the use of the processors.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Richard Thompson" <sbc@.thompson.co.za> wrote in message
>> news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
>> Hi,
>> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
>> license. I have now purchased an additional enterprise CPU license and
>> would like to `activate` an additional CPU.
>> How would I do this?
>> Many thanks
>> Richard
>