0

Let's assume the following situation:

  1. I have a database server that uses 4 core CPU;
  2. My machine has 2 core CPU;
  3. Assume they are of equal speed in terms of GHZ;
  4. Systems are connected over a network (two lines 200mb/s each);

Test tool that I use provides # of threads parameter and will issue commands in parallel to the server.

QUESTIONS:

  1. How would you test parallel reads/writes via stored procedure? Please brainstorm as any advice is appreciated;
  2. How can I prove that many threads are executing the queries on the server (or should I not pay attention to this as this servers and DB's responsibility)?
  3. What controls how many threads are executed at any time primarily in case of SQL server? I checked the "server properties" > processors > # of processors and threads section - waht more should I check?
  4. How can I check that my application truly executes on all my machine cores - in other words - uses real threads instead of virtual ones? Or should I pay attention only to the virtual ones?
  5. Should I pay attention to the network bandwidth? Can it be a bottleneck (I dont' send any big data, only commands with variables).
Improve this question
2
  • 1
    SQL Server is generally I/O bound, not CPU bound. If you're testing for performance, concentrate on I/O Commented Jun 12, 2014 at 12:22
  • @ElectricLlama thank you for the response. I guess what I really would like to do it to check whether as I increase the thread count that call a stored procedure on my machine - the same happens on the SQL server side. Can I be confident that this happens in development environment if it does on my local machine? Commented Jun 13, 2014 at 7:21

1 Answer 1

Reset to default
1
  • 1.) not sure perhaps someone else can answer
  • 2.) SQL Sentry allows you to monitor your SQL activity (use the free trial and buy if you like it)
  • 3.) Max Dop controls the number of processors & also the cost threshold will affect parrallelism
  • 4.) Same as 2 perhaps, i'm not sure i understand
  • 5.) Depends on what you are doing are where you see aproblem SQL sentry will show wait stats that may help
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.