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 unique keys

Would anyone please instruct how to prevent the duplicate record by
setting the unique keys on the ms sql server? i've been checking the
duplicate record as front-end and i found out if there is an internet
delay or some other reasons, it has a chance to store the duplicated
data into the database. so i realized it has to be done on the back-end
side.
for example, if i have three columns (office code, office id, office
section) as a unique key, how can i setup this? thanks in advance.create a primary key or a unique constraint

ALTER TABLE [dbo].[YourTable] WITH NOCHECK ADD
CONSTRAINT [YourTable_PK] PRIMARY KEY CLUSTERED
(
[office code],
[office id],
[office section]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

or

ALTER TABLE dbo.YourTable ADD CONSTRAINT
IX_YourTable UNIQUE NONCLUSTERED
(
[office code],
[office id],
[office section]

) ON [PRIMARY]

Denis the SQL Menace
http://sqlservercode.blogspot.com/
HandersonVA wrote:

Quote:

Originally Posted by

Would anyone please instruct how to prevent the duplicate record by
setting the unique keys on the ms sql server? i've been checking the
duplicate record as front-end and i found out if there is an internet
delay or some other reasons, it has a chance to store the duplicated
data into the database. so i realized it has to be done on the back-end
side.
for example, if i have three columns (office code, office id, office
section) as a unique key, how can i setup this? thanks in advance.