Struggling in poor performance

Question

Struggling in poor performance

alavdeens 76586

Newbie

Points: 6
More actions
April 14, 2015 at 2:07 am

#302941

I have a production server which is having some just 4 million records. The client always need the searching of an item which containing the history of records since 2008. So whenever after hit a particular item , it will show all history since from 2008 to current year date. It has being accessed from three remote locations simultaneously. But whenever they hit an item in the remote side, it hits my mind because of poor performance. All index parts have already done. I need the advice in the rest of other side in the performance. Looking forward for favorable advices... Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Poor performance is usually queries that can't use indexes or indexes which don't support the queries.

Remote access might be network latency, but if it is you'll need to talk to the network people, that's something that can't really be fixed from the SQL side

We can't really help much with so little details. Please can you post the query, the table and index definitions and the execution plan of the query (as a .sqlplan file)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MadAdmin SSChampion Points: 11261 More actions · Answer 2

What you can do to give Gail at least a bare minimum of information is either

1) select top 10 from sys.dm_exec_procedure_stats order by totalelapsedtime desc, and find the offending proc, script it out, maybe try anonymise table names and let us see the structure of the query.

2) if you dont have procs, then select top 10 * from sys.dm_exec_query_stats, join to query text etc.

3) you can ask users which page is slow, then go to QA and click about and run a trace and see what the DB is doing. Not the best way due to disparity between plans due to stats and table size etc, but better than guessing.

Else it is like reading minds.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

alavdeens 76586 Newbie Points: 6 More actions · Answer 3

Yes , am waiting for to connect the server. Once i get it then i will produce the clean report.

Thanks

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 4

what are they using to produce this sql? Can you the run same sql from query analyzer and see same slowness?