Using SQL Profiler to Troubleshoot my Slow Web performance

Question

I used SQL profiler to help me identify what was going on with the Database during the load of my netperfmon home page.  I determined that the All Nodes "ajax" web resource was the cause of my home page taking greater than 30 sec to load.  Using sql profiler I noticed that the following queries were run to populate the page. It seems to me that it select's * from the interface table for every interface on every node.  I have 108 nodes and 10,000 interfaces.  Is this by design?  Thanks

exec sp_executesql N'SELECT * FROM Views WHERE ViewID=@ViewID';, N'@ViewID int', @ViewID = 9

SELECT * FROM Nodes

I see this query for every node...exec sp_executesql N'SELECT InterfaceID FROM Interfaces WHERE NodeID=@NodeID';, N'@NodeID int', @NodeID = 32

I see this query for every interface......exec sp_executesql N'SELECT * FROM Interfaces WHERE InterfaceID=@interface';, N'@interface int', @interface = 951

Thanks

Brandon

Charter Communications

rvsraghu · Answer

HI

What is "N" which is getting repeated every time. why is that added and what is the use of that.

Regards,
Raghuram

tdanner · Answer

The GroupStatus/StatusDescription fields did occur to me when I was looking at that code. I'll ask around and see if anyone knows why we aren't using those. If nobody has a good reason, I'll see about changing it in a future release. It would definitely be faster.

bwicks · Answer

cool.. thanks for checking.. yeah I do not have any grouping setup in my All nodes Tree.  Adding a grouping did the trick. Looks like I was selecting the wrong All Nodes resource when not wanting to group by anything.

Thanks for your help Tim!!