SQL Server Performance Forum – Threads Archive
SQL Performance on Cluster
I am trying to test the performance of our sql server cluster that is running on a windows 2003 2 node cluster. Here is what I have done:1) put ~12 million rows in 2 tables that have a common GUID field.
2) put a primary key on the guid for both tables.
3) created an indexed view on a seperate file group
4) created index on guid for indexed view
5) created second index on view for 2 fields for selection criteria. Ran a query that will select about 90 thousand rows based on the second index and put them into a table that exist on a seperate filegroup. Results: when running from my local computer connected to the virtual server it takes about 3:40 seconds.
When running from the active node of the cluster connected to the virtual server the same query takes only 1:25 seconds. I have run these queries with different selection values that bring back different data but similar row counts so that I am not being affected by the cache. I have run performance monitor on the active node and the processor never goes above 30% and I am getting reads and writes at the same time due to using seperate file groups for the tables. I know you are all wondering is there a question here, and yes there is and here they are. 1) why would I see such a drastic difference running from my computer vs running from the active node of the cluster?
2) Does all of the processing happen on the server or does some of it happen on my local system?
3) Does the connection from my computer to the cluster make a difference?
Art
There are several reasons you may see a difference: 1) Running over the network can cause a performance hit, especially if the network is not well tuned. 2) The size of the hardware, especially memory size, of where the query is runnning (local or remote) can made a difference in how fast the query runs. Virtually all the processing takes place on the server, but the server still has to wait for the client to accept the data it is sent. 3) You may or may not be comparing apples to apples. When testing like this, you have to keep everything exactly the same, except for the one dependent variable you are testing. This is not always obvious or easy. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Thank you for your input, just 1 question. I am running the following query: INSERT INTO [Table]
SELECT * FROM [View]
WHERE [Field1] = ‘100010’ AND [Field2]= 86 I am not selecting the data to be displayed on the screen. Am I still going to be affected by my local computer and connection?
Art
]]>