Hi Everyone,

I recently built and launched a Dotnetnuke-based website for a high profile Automobile brand in Australia.

We have launched the site and have been receiving complaints from the hosting company advising of relatively high resource usage on the database.

I am not a DBA, so the message below is not very useful to me. Now I realise I may have to contact a DBA to do the suggested work, but if anyone here can advise me on the best action, it would be greatly appreciated.

Kind Regards,

James Beattie
Snr Web Developer

EMAIL FROM WEB HOSTS:

Hi

The data which is analysed is only captured twice a week as collecting the data on more frequent intervals is reserved for more specific Profiling requests where specific data is collected on a higher frequency to allow further investigation. The reporting we base our investigation on is designed to identify databases above our thresholds and that allows us to investigate further and provide some information to yourself. Then if required further profiling can be performed to further identify possible tuning which can be performed to optimize the database further.

I have looked back over the reported data which we have for your database xxxxx over the last month and I have provided the information from 2 days where the data was captured and your database reported usage above our thresholds.  The following tables outline the top queries captured on the dates listed.

---

 

10/01/2007 - xxxxxx

Query (example)		Batches /Min	CPU(ms) /Run	Duration (ms)/Run	Reads /Run
exec sp_reset_connection		344	0	5	0
exec dbo.GetHostSettings		236	0	80	3
exec dbo.GetTabsByParentId @ParentId=7		34	3	3293	68
exec dbo.GetModulePermissionsByModuleID @ModuleID=438,@PermissionID=-1		16	1	947	12
exec dbo.GetHtmlText @ModuleId=932		4	0	84	7
exec dbo.GetSearchItems @PortalId=NULL,@TabId=NULL,@ModuleId=747		3	8	8616	3265
exec dbo.GetTabPermissionsByPortal @PortalID=0		2	11	12848	1914
exec dbo.GetPortal @PortalId=0		2	2	2775	33
exec dbo.GetTabs @PortalId=0		1	415	421683	2039
exec dbo.GetSchedule @Server='WIC022U'		1	27	26880	1189
exec dbo.GetScheduleNextTask @Server='WIC022U'		1	27	25256	1126
exec dbo.GetSearchModules @PortalID=0		1	344	1502938	18335

 

17/01/2007 - xxxxx

Query (example)		Batches /Min	CPU(ms) /Run	Duration (ms)/Run	Reads /Run
exec sp_reset_connection		54	0	5	0
exec dbo.GetTabsByParentId @ParentId=7		15	3	3236	68
exec dbo.GetModulePermissionsByModuleID @ModuleID=375,@PermissionID=-1		11	1	1109	12
exec dbo.GetHtmlText @ModuleId=727		4	0	88	7
exec dbo.GetSearchItems @PortalId=NULL,@TabId=NULL,@ModuleId=747		3	10	9634	3265
exec dbo.GetTabPermissionsByPortal @PortalID=0		2	15	14190	2266
		1	1	1467	51
exec dbo.GetTab @TabId=84		1	2	2313	13
exec dbo.GetTabs @PortalId=0		1	402	402005	2039
exec dbo.GetSearchModules @PortalID=0		1	390	420216	18335
exec dbo.GetSchedule @Server='WIC022U'		1	23	22680	1040
exec dbo.GetScheduleNextTask @Server='WIC022U'		1	20	21855	984

---

 From this information  you can see that there are 2 main Stored Procedures which are resulting in high amounts of CPU, which I have highlighted in bold. I have noticed that when these Stored Procs are not listed in the report your CPU usage is only listed at a warning level, which would indicate that these stored procs may not be used on a daily basis however they do appear in the reports on the days we have captured the data.

After looking into the queries which are run by the Stored Procs 'dbo.GetTabs' and 'dbo.GetSearchModules' I have found that although there are some indexes configured for the Tables used by those Stored Procs there are also a number of fields which are referenced in clauses such as JOINS, WHERE, ORDER BY, and that is resulting in Table Scans and other expensive steps in the Execution Plan. The following is a few of the tables that I have found which require further indexing and may be used to assist with your own investigation / optimization:

Table: dbo.tabs
current indexes:
IX_Tabs_1 on 'PortalID'
IX_Tabs_2 on 'ParentId'
From what I have found I would suggest also indexing 'TabOrder', 'TabName', 'URL', 'IconFile', 'TabId'

Table: dbo.TabModules
current indexes:
IX_TabModules on 'TabID', 'ModuleID'
From what I have found I would suggest also indexing 'TabModuleId', 'ModuleOrder', 'IconFile' 

Ideally the indexing of your database should be performed by someone who is familar with the application as it requires knowledge of what statements are used across the application to ensure that the indexes being created allow for all of those statements.

Please let us know when you have improved the optimization of your database so that we can monitor the affect it has from our data which we capture. 

If you have any further queries, feel free to respond to this job.

You might want to talk with John Mitchell from http://www.snapsis.com

 John's knowledge on compression (and caching) is outstanding and his PageBlaster module has been well received by many users.

In my opinion, and based on what I've heard from many people, even though the DNN 4.4.1 build is fast, on larger sites you need to consult with experienced people who know how to make the application run faster in a commercial environment. I think you'll find the AFL site will be running DNN in the next 48 hours, and it was running very poorly until they got in touch with John and the peformance changes were very impressive to say the least. I can't share them with you but I am hoping that it will be publicised since it's hard to believe how good the improvements were by using his compression technology.

I'm interested to know what major car manufacturer in Australia would be using DNN.

Nina Meiers
My Site  -  My Blogs  - Goodies - DNNSkins - Nina's Free Skins 

//edited - Omitted the other important part of the equation - caching //

---

I would like to know the numbers from these queries:

 

 select portalid from dbo.portals order by portalid

select portalid, count(*) from dbo.modules group by portalid order by portalid

select t.portalid, count(*) from dbo.tabmodules as tm
inner join dbo.tabs as t on tm.tabid = t.tabid
group by t.portalid order by t.portalid

select portalid, count(*) from dbo.files group by portalid order by portalid

select t.portalid, count(*) from dbo.tabmodules as tm
inner join dbo.tabs as t on tm.tabid = t.tabid
where upper(tm.iconfile) like 'FILEID=%'
group by t.portalid order by t.portalid