SQL Server Performance Forum – Threads Archive

More sessions = more performance?

I have a SP that I run in query analyzer that runs with a certain throughput. If I open a second session and run another copy of the SP I get twice the throughput. Open a 3rd and get 3X and so on. The problem is that I want to maximize the throughput on the first session. I don’t want other sessions. This is obviously not I/O bound since more sessions=more performance. Why doesn’t SQL Server run the first SP in the first session with the most resources availble? Thanks,
vshawk

I don’t necessarily think it’s the sessions. When you run it like this, the procedure plan will already be in the procedure cache. You likely even have the data in memory that’s needed. This would drastically speed up later runs. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Those sessions are meant to be the existing connections and in order to get the performance ensure the used SPs are recompiled and involved table indexes were reorganised. Run SP_WHO2 to get the details for that session. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Sorry guys, But, I am still a little confused. By "session" I meant a new Query window in Query Analyzer. I assume each new window is a new session/connection. So, when I open multiple windows and run multiple copies of the same SP, I get more throughput. By throughput I mean transactions/second — or inserts into a destination table. I really only want to run 1 connection (or Query window) and get the most throughput possible. Why do I get more through put when I open more windows and run more copies of the same SP? Thanks again,
vshawk

As Derri said, first time you run sp, SQL put the execution plan in cache.
Second time, plan is allready there, so run fast. If you are planning to put sp in production, will work in the same way like before, I means first time will take longer time than second. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks everyone, But I don’t think I am making myself clear. These sessions are not running serially — not one after another. These sessions are running in parallel.
I guess I could see how you would some inprovement on the second session because the execution plan is in cache. But, I don’t think that explains why if I run 3, 4, 5 or more I 3x, 4x, 5x (and so on) throughput. Thanks again …
vshawk

What do you mean with throughput. Do you return 10000 rows 2x faster, 3x faster, 4x faster etc? Could you show some numbers representing this. I can’t see how adding more connections/sessions would increase throughput in that constant scale all the time. Only one time and that’s when the execution plan is cached. If so then you could have 100 connections/sessions and it would be 100 times as fast. I really don’t think that is the case. I see no relation here that 10 connections/session would cause a 10x performance increase.

Good point, Arqyle. I would like to see examples too.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Also I suggest to acquire PERFMON (SYSMON) counters : SQL Server<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />atabases – Transactions/sec SQL Server<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QL Statistics – SQL Compilations/sec SQL Server:Memory Manager – Connection Memory SQL Server:Locks – Average Wait Time SQL Server<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />atabases – Active Transactions SQL Server:Cache Manager – Cache hit ratio SQL Server:Buffer Manager – Buffer cache hit ratio Transactions/sec and Batch Requests/sec are identical. Thus, you don’t need to issue an explicit pair of BEGIN/COMMIT statements to make SQL Server report the batch as having started a transaction. Of course, all UPDATE, INSERT, and DELETE statements are transactionally consistent and operate within the scope of a transaction. <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

]]>