SQL Server 2005 - performance

[Techno]

I have tried to performance tune SQL Server 2005 database to unfortunately try and take a hit of absurd queries generated by the client and LINQ to SQL. But it apperently hasnt made any difference in terms of performance improvement.

it did state it would improve performance by about 77% - but supposidly there isnt much of a difference

so, anything else I can do to improve the SQL performance?

LINQ generates an unreal query unfortunately to do a search on multiple tables. This causes the whole thing to slow down of course but we cannot change LINQ, whilst there are plans to make a DAL from scratch in the ASP.NET application, until then we cannot do much.

[GaryMazzone]

Have you looked at locking or blocking? What about waits for reasources? I/O waits? CPU waits?

[Techno]

there are no record blockings going on. It's more of a CPU wait I guess because the CPU spikes up when we have say 2 users do different searches on the same page... which isnt scalable considering there are meant to be 400 users at any one time as maximum! otherwise it will time out... stupid, i know

[GaryMazzone]

There is a tool on codeplex called SQLNexus

http://www.codeplex.com/sqlnexus

I have used this to get information and help me figure out what is going on. It will capture PerfMon stats and SQL server stats (there is a setof scripts to run to gather the need data). It shows you stuff in a graphical format. I have used it to gather data on our production systems to look for imporvement spots.

I would say give it a try. Get the traces started then try and have a couple of people log in and try a search.

The tool is from some MS engineers.

[Techno]

thanks, much appreciated! i will give this a bash tomorrow. SQL is fine, just the unfortunate poor coding of developers and LINQ knocking out the CPU