Skunkers, Here's the scenario: There is a core application that is build on a 2 tier client/Server architecture. Total number of users at any one time over a WAN is approx 100. The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows: 1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations The problem: 1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt. *What are the likely contributors to performance degradation*?
What platform is your app built on? Making a guess here, you need to identify and isolate your potential bottlenecks, here are my suggestions in order of culpability: 1. Database:- Watch that database like a hawk! I would bet my money on it being the culprit. There's this app called Spotlight on SQL Server<http://www.quest.com/spotlight-on-sql-server-enterprise/>, see if you can get your hands on one. 2. Application Platform:- Is it .Net, Java etc, each comes with its own "environment" which could be the culprit 3. Operating System 4. Network On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
See my responses On Tue, Nov 29, 2011 at 10:43 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
What platform is your app built on?
Making a guess here, you need to identify and isolate your potential bottlenecks, here are my suggestions in order of culpability:
1. Database:- Watch that database like a hawk! I would bet my money on it being the culprit. There's this app called Spotlight on SQL Server<http://www.quest.com/spotlight-on-sql-server-enterprise/>, see if you can get your hands on one.
ok
2. Application Platform:- Is it .Net, Java etc, each comes with its own "environment" which could be the culprit
VB. Form based not web based app
3. Operating System
WIN2k3
4. Network
No sure I understand this bit of the qstn
On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Sorry, I wasn't really asking you a question, I was giving my opinion on where you should start looking for the source of your problems, i.e. start by checking if the database is the culprit, then move to the platform etc However, given @sobbayi comments, maybe you might want to bump network to number 2. Reports are generally data heavy, which equals increased database and bandwidth use. On Tue, Nov 29, 2011 at 10:54 AM, muskiv <kulebak@gmail.com> wrote:
See my responses
On Tue, Nov 29, 2011 at 10:43 AM, Peter Karunyu <pkarunyu@gmail.com>wrote:
What platform is your app built on?
Making a guess here, you need to identify and isolate your potential bottlenecks, here are my suggestions in order of culpability:
1. Database:- Watch that database like a hawk! I would bet my money on it being the culprit. There's this app called Spotlight on SQL Server<http://www.quest.com/spotlight-on-sql-server-enterprise/>, see if you can get your hands on one.
ok
2. Application Platform:- Is it .Net, Java etc, each comes with its own "environment" which could be the culprit
VB. Form based not web based app
3. Operating System
WIN2k3
4. Network
No sure I understand this bit of the qstn
On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On Tue, Nov 29, 2011 at 11:01 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
Sorry, I wasn't really asking you a question, I was giving my opinion on where you should start looking for the source of your problems, i.e. start by checking if the database is the culprit, then move to the platform etc
Noted
However, given @sobbayi comments, maybe you might want to bump network to number 2.
I clarified @sobbayi's network concern
Reports are generally data heavy, which equals increased database and bandwidth use.
Thx
On Tue, Nov 29, 2011 at 10:54 AM, muskiv <kulebak@gmail.com> wrote:
See my responses
On Tue, Nov 29, 2011 at 10:43 AM, Peter Karunyu <pkarunyu@gmail.com>wrote:
What platform is your app built on?
Making a guess here, you need to identify and isolate your potential bottlenecks, here are my suggestions in order of culpability:
1. Database:- Watch that database like a hawk! I would bet my money on it being the culprit. There's this app called Spotlight on SQL Server<http://www.quest.com/spotlight-on-sql-server-enterprise/>, see if you can get your hands on one.
ok
2. Application Platform:- Is it .Net, Java etc, each comes with its own "environment" which could be the culprit
VB. Form based not web based app
3. Operating System
WIN2k3
4. Network
No sure I understand this bit of the qstn
On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
you my friend need something like Citrix , well on the other hand check if the DB is well indexed (consult a MSSQL guru), make sure your queries are optimized (i.e. there are cases you can use views instead of querying a whole table ) assuming you don't have them and finally get your server logs !!!! and analyse them
From an admin point of view
On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- GG
See my responses On Tue, Nov 29, 2011 at 10:44 AM, geoffrey gitagia <ggitagia@gmail.com>wrote:
you my friend need something like Citrix ,
Does it always have to go the citrix way.......shld be the last result may be
well on the other hand check if the DB is well indexed (consult a MSSQL guru),
Ok
make sure your queries are optimized (i.e. there are cases you can use views instead of querying a whole table ) assuming you don't have them and finally get your server logs !!!! and analyse them
I'll need to further investigate the logic behind the queries. Thx
From an admin point of view
On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- GG
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Your system configuration for one could be the problem. You have mentioned what hardware you have but you have not told us how the system is configured and what other software and services are running on that server and network eg you might have 40 GB RAM but how much is actually reserved for the DB ? Then looking at your Bandwidth 256k (32KBps) for 100 users... thats a pushing it dont you think? For all intents and purposes your hardware should not be a problem with those specs especially on SQL Server 2005. Steve ----- Original Message ----- | From: "muskiv" <kulebak@gmail.com> | To: "Skunkworks forum" <skunkworks@lists.my.co.ke> | Sent: Tuesday, November 29, 2011 10:34:16 AM | Subject: [Skunkworks] Performance degradation - Help sought | Skunkers, | Here's the scenario: | There is a core application that is build on a 2 tier client/Server | architecture. | Total number of users at any one time over a WAN is approx 100. | The app runs on a MS SQL 2005 DB that resides on a Primary server | whose specs are as follows: | 1. RAM: 40 GB | 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors | 3. Network Interface: Gigabit ethernet card | 4. RAID 5 config for the main DB drives - where the main DB files | reside. | 5. RAID 1 + 0 config for the log files on a seperate disk | 6. WAN traffic is on 256k/256k links from satellite locations | The problem: | 1. As users are being added into the WAN, performance has been | deteriorating. | 2. On report generation, everything seems to crawl to a halt. | What are the likely contributors to performance degradation ? | _______________________________________________ | Skunkworks mailing list | Skunkworks@lists.my.co.ke
Steve, see my responses On Tue, Nov 29, 2011 at 10:48 AM, Steve Obbayi <steve@sobbayi.com> wrote:
Your system configuration for one could be the problem. You have mentioned what hardware you have but you have not told us how the system is configured and what other software and services are running on that server and network
The server is NOT running AD or any other of those services. Its dedicated to the DBMS. Any other specific services that you'd like to highlight?
eg you might have 40 GB RAM but how much is actually reserved for the DB?
From the little understanding that I have, SQL intelligently reserves memory for itself even without intervention.
Then looking at your Bandwidth 256k (32KBps) for 100 users... thats a pushing it dont you think?
Let me correct that. The 100 users are acros 13 sites with each having roughly 8 - 10 users. Each of these sites has a 256K link
For all intents and purposes your hardware should not be a problem with those specs especially on SQL Server 2005.
Steve
------------------------------
*From: *"muskiv" <kulebak@gmail.com> *To: *"Skunkworks forum" <skunkworks@lists.my.co.ke> *Sent: *Tuesday, November 29, 2011 10:34:16 AM *Subject: *[Skunkworks] Performance degradation - Help sought
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
@muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application. You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs. Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
Aki On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote:
@muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application.
Sorry how do I do this in WIN2K3?
You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs.
Same question as above. (Sorry I know I can google :)
Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything Thx
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
@Muskiv, Thank you for not believing in google search. Please ask the network group to shed more inputs as they have CCIE level people who would know much more about how fast packets should move. However, I shall give you an input on application acclereration and WAN optimization. A rule of the thumb is that these devices are a must use between local and regional hubs/offices. They have been around for years, use proprietary techniques that shape or break the data into smaller chunks for fast delivery and unpacking. You can also go into packet tracing and learn more about the number of sessions requests that the DB is causing, including duration and overhead. How much data is moving in each session is also possible. You'd seriously need something like an ISA server platform to get some real reporting features that are available of much more expensive devices, and not at all on free-code systems. Some months ago I was reading on this : http://www.cisco.com/en/US/prod/collateral/contnetw/ps5680/ps6474/data_sheet... and what it can do. Please bookmark the link. No silly freedom/open source systems even come close to proprietary total innovation that drives the world's advancement of tech sectors. Ofcourse there will be attempts at making it. I'd like anyone on this list to recommend an equivalent free-code system that I'd be interested to look into, which can also help @Muskiv deploy better WANs. The other guys have very good suggestions too, please also take a look into their replies. Rgds. :-) On Tue, Nov 29, 2011 at 11:12 AM, muskiv <kulebak@gmail.com> wrote:
Aki
On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote:
@muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application.
Sorry how do I do this in WIN2K3?
You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs.
Same question as above. (Sorry I know I can google :)
Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything
Thx
Dear Skunks/ Skuntekktes, There is a networking dinner for Entrepreneurs at Serena Hotel Nairobi ,organized by Strathmore Alumni Entrepreneurs Network. You can buy a ticket at www.myticket.co.ke . You can contact me off-list on oscar@oscarkimani.com for any inquiry All are invited Thanks O.kimani From: skunkworks-bounces@lists.my.co.ke [mailto:skunkworks-bounces@lists.my.co.ke] On Behalf Of aki Sent: Tuesday, November 29, 2011 11:42 AM To: Skunkworks Mailing List Subject: Re: [Skunkworks] Performance degradation - Help sought @Muskiv, Thank you for not believing in google search. Please ask the network group to shed more inputs as they have CCIE level people who would know much more about how fast packets should move. However, I shall give you an input on application acclereration and WAN optimization. A rule of the thumb is that these devices are a must use between local and regional hubs/offices. They have been around for years, use proprietary techniques that shape or break the data into smaller chunks for fast delivery and unpacking. You can also go into packet tracing and learn more about the number of sessions requests that the DB is causing, including duration and overhead. How much data is moving in each session is also possible. You'd seriously need something like an ISA server platform to get some real reporting features that are available of much more expensive devices, and not at all on free-code systems. Some months ago I was reading on this : http://www.cisco.com/en/US/prod/collateral/contnetw/ps5680/ps6474/data_sheet _c78-495801.html and what it can do. Please bookmark the link. No silly freedom/open source systems even come close to proprietary total innovation that drives the world's advancement of tech sectors. Ofcourse there will be attempts at making it. I'd like anyone on this list to recommend an equivalent free-code system that I'd be interested to look into, which can also help @Muskiv deploy better WANs. The other guys have very good suggestions too, please also take a look into their replies. Rgds. :-) On Tue, Nov 29, 2011 at 11:12 AM, muskiv <kulebak@gmail.com> wrote: Aki On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote: @muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application. Sorry how do I do this in WIN2K3? You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs. Same question as above. (Sorry I know I can google :) Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level? A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything Thx
Dear Skunks/ettes, Sorry for the mistake..Its Sarova Panafric Thanks Oscar From: skunkworks-bounces@lists.my.co.ke [mailto:skunkworks-bounces@lists.my.co.ke] On Behalf Of Kimani : TransTech " Love For Tech Stuff" Sent: Tuesday, November 29, 2011 12:52 PM To: 'Skunkworks Mailing List' Subject: [Skunkworks] Entrepreneurs Cocktail at Serena Nairobi on 2nd Dec 2011 Dear Skunks/ Skuntekktes, There is a networking dinner for Entrepreneurs at Serena Hotel Nairobi ,organized by Strathmore Alumni Entrepreneurs Network. You can buy a ticket at www.myticket.co.ke . You can contact me off-list on oscar@oscarkimani.com for any inquiry All are invited Thanks O.kimani From: skunkworks-bounces@lists.my.co.ke [mailto:skunkworks-bounces@lists.my.co.ke] On Behalf Of aki Sent: Tuesday, November 29, 2011 11:42 AM To: Skunkworks Mailing List Subject: Re: [Skunkworks] Performance degradation - Help sought @Muskiv, Thank you for not believing in google search. Please ask the network group to shed more inputs as they have CCIE level people who would know much more about how fast packets should move. However, I shall give you an input on application acclereration and WAN optimization. A rule of the thumb is that these devices are a must use between local and regional hubs/offices. They have been around for years, use proprietary techniques that shape or break the data into smaller chunks for fast delivery and unpacking. You can also go into packet tracing and learn more about the number of sessions requests that the DB is causing, including duration and overhead. How much data is moving in each session is also possible. You'd seriously need something like an ISA server platform to get some real reporting features that are available of much more expensive devices, and not at all on free-code systems. Some months ago I was reading on this : http://www.cisco.com/en/US/prod/collateral/contnetw/ps5680/ps6474/data_sheet _c78-495801.html and what it can do. Please bookmark the link. No silly freedom/open source systems even come close to proprietary total innovation that drives the world's advancement of tech sectors. Ofcourse there will be attempts at making it. I'd like anyone on this list to recommend an equivalent free-code system that I'd be interested to look into, which can also help @Muskiv deploy better WANs. The other guys have very good suggestions too, please also take a look into their replies. Rgds. :-) On Tue, Nov 29, 2011 at 11:12 AM, muskiv <kulebak@gmail.com> wrote: Aki On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote: @muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application. Sorry how do I do this in WIN2K3? You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs. Same question as above. (Sorry I know I can google :) Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level? A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything Thx
Thanks Aki for the insights. I'll take some time and do some reading on the Cisco WAVE On Tue, Nov 29, 2011 at 11:41 AM, aki <aki275@gmail.com> wrote:
@Muskiv,
Thank you for not believing in google search. Please ask the network group to shed more inputs as they have CCIE level people who would know much more about how fast packets should move. However, I shall give you an input on application acclereration and WAN optimization. A rule of the thumb is that these devices are a must use between local and regional hubs/offices. They have been around for years, use proprietary techniques that shape or break the data into smaller chunks for fast delivery and unpacking. You can also go into packet tracing and learn more about the number of sessions requests that the DB is causing, including duration and overhead. How much data is moving in each session is also possible. You'd seriously need something like an ISA server platform to get some real reporting features that are available of much more expensive devices, and not at all on free-code systems.
Some months ago I was reading on this : http://www.cisco.com/en/US/prod/collateral/contnetw/ps5680/ps6474/data_sheet... and what it can do. Please bookmark the link. No silly freedom/open source systems even come close to proprietary total innovation that drives the world's advancement of tech sectors. Ofcourse there will be attempts at making it. I'd like anyone on this list to recommend an equivalent free-code system that I'd be interested to look into, which can also help @Muskiv deploy better WANs.
The other guys have very good suggestions too, please also take a look into their replies.
Rgds. :-)
On Tue, Nov 29, 2011 at 11:12 AM, muskiv <kulebak@gmail.com> wrote:
Aki
On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote:
@muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application.
Sorry how do I do this in WIN2K3?
You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs.
Same question as above. (Sorry I know I can google :)
Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything
Thx
Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On Tue, Nov 29, 2011 at 11:41 AM, aki <aki275@gmail.com> wrote: [...]
world's advancement of tech sectors. Ofcourse there will be attempts at making it. I'd like anyone on this list to recommend an equivalent free-code system that I'd be interested to look into, which can also help @Muskiv deploy better WANs.
Aki, http://www.trafficsqueezer.org/ Muskiv, I've seen several useful suggestions given before, my 2 cents is that you need to compare the performance of the app on the LAN as opposed to across the WAN. A few questions related to WAN performance: (1) Are you monitoring traffic across your WAN? In terms of traffic types, is the WAN dedicated to this app or are there other types of traffic traversing your WAN (Internet, VOIP etc)? Depending on the network equipment you have you could prioritize the app-specific traffic. (2) Also, monitoring in terms of volume - are your WAN links saturated? (3) A previous post mentioned the network characteristics of the app. Using netstat you may be able to view the connections to the db/app and probably see how they're opened/closed. I vaguely remember a similar problem where investigations showed that the app was keeping connections open long after the client disconnected, leading to memory issues after while until the service was restarted. Good luck. BR, S -- This message represents the official view of the voices in my head.
Is the problem replicated at the hq/head office? Because if its still slow there then the bandwidth issue may not be the concern. Then, on report generation, where is the reporting done? Are u using stored procedures? Though unlikely, it could be that the logic is in the vb app as opposed to it running on the SQL server. My advice, as has been noted by other listers: a) Check your queries. For shared db some reports dont have to be regenerated for every user b) Check your network. If the reports are generated fast enough, then its the serving thats the problem c) Maybe the application/platform itself On Tue, Nov 29, 2011 at 10:12 AM, muskiv <kulebak@gmail.com> wrote:
Aki
On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote:
@muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application.
Sorry how do I do this in WIN2K3?
You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs.
Same question as above. (Sorry I know I can google :)
Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything
Thx
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Mr. Lawi On Tue, Nov 29, 2011 at 11:45 AM, Mr. Lawi <mail2lawi@gmail.com> wrote:
Is the problem replicated at the hq/head office?
NO
Because if its still slow there then the bandwidth issue may not be the concern.
Then, on report generation, where is the reporting done?
As in location or device?.....If device reporting done from Main DB server.
Are u using stored procedures?
Yes...Most likely the SQL logic is in Stored procedures
Though unlikely, it could be that the logic is in the vb app as opposed to it running on the SQL server.
My advice, as has been noted by other listers: a) Check your queries. For shared db some reports dont have to be regenerated for every user
OK
b) Check your network. If the reports are generated fast enough, then its the serving thats the problem
Did U mean server? c) Maybe the application/platform itself
Thx
On Tue, Nov 29, 2011 at 10:12 AM, muskiv <kulebak@gmail.com> wrote:
Aki
On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote:
@muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application.
Sorry how do I do this in WIN2K3?
You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs.
Same question as above. (Sorry I know I can google :)
Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything
Thx
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On Tue, Nov 29, 2011 at 1:48 PM, muskiv <kulebak@gmail.com> wrote:
Mr. Lawi
On Tue, Nov 29, 2011 at 11:45 AM, Mr. Lawi <mail2lawi@gmail.com> wrote:
Is the problem replicated at the hq/head office?
NO
This points to network issues. You should explore Aki's options (and other network suggestions provided).
Because if its still slow there then the bandwidth issue may not be the
concern.
Then, on report generation, where is the reporting done?
As in location or device?.....If device reporting done from Main DB server.
This fine. Maybe you can also do a few checks using SQL Profiler - check the time it takes to service a request
Are u using stored procedures?
Yes...Most likely the SQL logic is in Stored procedures
Good
Though unlikely, it could be that the logic is in the vb app as opposed to
it running on the SQL server.
My advice, as has been noted by other listers: a) Check your queries. For shared db some reports dont have to be regenerated for every user
OK
b) Check your network. If the reports are generated fast enough, then its the serving thats the problem
Did U mean server?
I meant the database server may be fast in accepting and processing the requests but serving the same to the end user takes time - coz of network.
c) Maybe the application/platform itself
Thx
On Tue, Nov 29, 2011 at 10:12 AM, muskiv <kulebak@gmail.com> wrote:
Aki
On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote:
@muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application.
Sorry how do I do this in WIN2K3?
You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs.
Same question as above. (Sorry I know I can google :)
Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything
Thx
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
is it a vb6 app :-).... i remember that most vb apps where never coded to use network resources efficiently i.e maintaining an active connection to the db as long as the app was running, instead of just when data transfer was needed. .net's data access methods alleviate this issue coz ado.net is desgined to open/close db connections automatically.... however, in some scenarios, that may become problematic if ur not careful.....i remember helping a pal improve an app he had. it wld update 10,000 records in just under an hour (on a dev laptop) because each single update per record wld make ado.net open a connection to the db, update that single record, then close the connection. opening/closing connections is expensively time consumig ng, and we slashed thd processing time fo just under a minute by batching up the updates, so only one db-connection would be used go update all the records..... .... in short, i'd bet on the app being the culprit, esp if reporg processing is done on the client-end. On 11/29/11, Mr. Lawi <mail2lawi@gmail.com> wrote:
On Tue, Nov 29, 2011 at 1:48 PM, muskiv <kulebak@gmail.com> wrote:
Mr. Lawi
On Tue, Nov 29, 2011 at 11:45 AM, Mr. Lawi <mail2lawi@gmail.com> wrote:
Is the problem replicated at the hq/head office?
NO
This points to network issues. You should explore Aki's options (and other network suggestions provided).
Because if its still slow there then the bandwidth issue may not be the
concern.
Then, on report generation, where is the reporting done?
As in location or device?.....If device reporting done from Main DB server.
This fine.
Maybe you can also do a few checks using SQL Profiler - check the time it takes to service a request
Are u using stored procedures?
Yes...Most likely the SQL logic is in Stored procedures
Good
Though unlikely, it could be that the logic is in the vb app as opposed to
it running on the SQL server.
My advice, as has been noted by other listers: a) Check your queries. For shared db some reports dont have to be regenerated for every user
OK
b) Check your network. If the reports are generated fast enough, then its the serving thats the problem
Did U mean server?
I meant the database server may be fast in accepting and processing the requests but serving the same to the end user takes time - coz of network.
c) Maybe the application/platform itself
Thx
On Tue, Nov 29, 2011 at 10:12 AM, muskiv <kulebak@gmail.com> wrote:
Aki
On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote:
@muskiv, imo, your problem is purely on the network layer. You need to see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application.
Sorry how do I do this in WIN2K3?
You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs.
Same question as above. (Sorry I know I can google :)
Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything
Thx
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Point noted brother. On Wed, Nov 30, 2011 at 9:21 AM, kevin karanja <kevinkarash@gmail.com>wrote:
is it a vb6 app :-).... i remember that most vb apps where never coded to use network resources efficiently i.e maintaining an active connection to the db as long as the app was running, instead of just when data transfer was needed. .net's data access methods alleviate this issue coz ado.net is desgined to open/close db connections automatically.... however, in some scenarios, that may become problematic if ur not careful.....i remember helping a pal improve an app he had. it wld update 10,000 records in just under an hour (on a dev laptop) because each single update per record wld make ado.net open a connection to the db, update that single record, then close the connection. opening/closing connections is expensively time consumig ng, and we slashed thd processing time fo just under a minute by batching up the updates, so only one db-connection would be used go update all the records.....
.... in short, i'd bet on the app being the culprit, esp if reporg processing is done on the client-end.
On 11/29/11, Mr. Lawi <mail2lawi@gmail.com> wrote:
On Tue, Nov 29, 2011 at 1:48 PM, muskiv <kulebak@gmail.com> wrote:
Mr. Lawi
On Tue, Nov 29, 2011 at 11:45 AM, Mr. Lawi <mail2lawi@gmail.com> wrote:
Is the problem replicated at the hq/head office?
NO
This points to network issues. You should explore Aki's options (and other network suggestions provided).
Because if its still slow there then the bandwidth issue may not be the
concern.
Then, on report generation, where is the reporting done?
As in location or device?.....If device reporting done from Main DB server.
This fine.
Maybe you can also do a few checks using SQL Profiler - check the time it takes to service a request
Are u using stored procedures?
Yes...Most likely the SQL logic is in Stored procedures
Good
Though unlikely, it could be that the logic is in the vb app as opposed to
it running on the SQL server.
My advice, as has been noted by other listers: a) Check your queries. For shared db some reports dont have to be regenerated for every user
OK
b) Check your network. If the reports are generated fast enough, then its the serving thats the problem
Did U mean server?
I meant the database server may be fast in accepting and processing the requests but serving the same to the end user takes time - coz of network.
c) Maybe the application/platform itself
Thx
On Tue, Nov 29, 2011 at 10:12 AM, muskiv <kulebak@gmail.com> wrote:
Aki
On Tue, Nov 29, 2011 at 11:05 AM, aki <aki275@gmail.com> wrote:
@muskiv, imo, your problem is purely on the network layer. You need
to
see the number of db sessions open in tcp to understand the overhead demanded by the client-server wan-wan application.
Sorry how do I do this in WIN2K3?
You way eventually have to install wan application and optimization accelerators since increasing the bandwidth on the remote sites would not justify costs.
Same question as above. (Sorry I know I can google :)
Have you also done local LAN tests on the seutp to determine the nbottlenecks at DB or hardware level?
A number of LAN tests have been done. I've noted a few that I think need resolution...work is in Progress. Just didn't want to miss anything
Thx
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Hey Here is a good checklist a. Check MaxMem for SQL Server - most times, SQL will only utilize 3GB mem even though the server has 40GB. You need to set it to use lets say 32GB so that when the instance starts, it grabs 32GB b. If you have big tables that you hit often, you can use DBCC pintable so that the table it pinned onto memory and you dont have IO expenses when you are getting data. This means that if you have 32GB RAM and the database is 10GB, you can pin ALL the table on RAM and speed up stuff by lets say 500% on the IO side... (http://msdn.microsoft.com/en-us/library/aa258284%28v=sql.80%29.aspx) Note: I have not used this feature in SQL 2005 and SQL 2008 but it was pretty popluar in high traffic environment c. Please ran a DBCC checkdb - this will ensure that there is not corruption on the db, tables, indexes etc. This is usually a nice starting point with SQL Server http://msdn.microsoft.com/en-us/library/ms176064.aspx d. Look into DBCC dbreindex to rebuild your indexes - this should be ran like daily ... http://msdn.microsoft.com/en-us/library/ms181671.aspx Some more thoughts. You issue as described is most likely because of the reports which usually have to read all the data. I would review the SQL in the reports and ensure I have the right indexes so that I am not scanning a whole table to find a record. Usually, when searching for a record, if there are no indexes, the database engine has to do a table scan... that means if you have 1 million records it will have to read all ... if you put indexes, they the query does not have to do a scan and it finds the data faster. Please spend some time on http://www.sql-server-performance.com/ and you can start with - http://www.sql-server-performance.com/2007/optimizing-indexes-general/ - http://www.sql-server-performance.com/2007/clustered-indexes/ For most reports - look into covering indexes but dont weka too many because they will slow down "writes" Thats that .... I have inboxed my CV if you feel like sending some money my way (Christmas Imefika bana) oh .. the issue is not the network .. On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
@Steve, thnks for that link, its made my day. I feel like carrying out a TKO-Mike Tyson style on this open source moron! :-))) Just listen to his video presentation, no clue and differentiation between CDN and application acceleration. Load balancing has nothing to do wan acceleration, neither does splitting requests, even you know this. In a nutshell, he is on about packet priorities and caching. If one does not know how proprietary apps move their client-server data which do not fall under CDN, how then does a network engineer implement priority on the packets. He has to go proprietary. Rgds.
Bw Liko the Mayor....at least I enjoyed reading the below. Thanks for the useful tips. I enjoy reading the MSDN links and I'll surely look up the ones below. See my responses below in red? Cheers On Thu, Dec 1, 2011 at 7:56 AM, Agosta Liko <agostal@gmail.com> wrote:
Hey
Here is a good checklist
a. Check MaxMem for SQL Server - most times, SQL will only utilize 3GB mem even though the server has 40GB. You need to set it to use lets say 32GB so that when the instance starts, it grabs 32GB
Assuming the code is the issue....how does an over allocation of memory help (unless B below answers it?)
b. If you have big tables that you hit often, you can use DBCC pintable so that the table it pinned onto memory and you dont have IO expenses when you are getting data. This means that if you have 32GB RAM and the database is 10GB, you can pin ALL the table on RAM and speed up stuff by lets say 500% on the IO side...
(http://msdn.microsoft.com/en-us/library/aa258284%28v=sql.80%29.aspx)
Note: I have not used this feature in SQL 2005 and SQL 2008 but it was pretty popluar in high traffic environment
c. Please ran a DBCC checkdb - this will ensure that there is not corruption on the db, tables, indexes etc. This is usually a nice starting point with SQL Server
Sawa
http://msdn.microsoft.com/en-us/library/ms176064.aspx
d. Look into DBCC dbreindex to rebuild your indexes - this should be ran like daily ...
http://msdn.microsoft.com/en-us/library/ms181671.aspx
Rebuilding of indexes on a daily basis. Don't you think this would be too
much? Takes quite some time to rebuild these. Rebuilding is being done once a month tho.
Some more thoughts.
You issue as described is most likely because of the reports which usually have to read all the data. I would review the SQL in the reports and ensure I have the right indexes so that I am not scanning a whole table to find a record. Usually, when searching for a record, if there are no indexes, the database engine has to do a table scan... that means if you have 1 million records it will have to read all ... if you put indexes, they the query does not have to do a scan and it finds the data faster.
Understood . I think after all remedial measures fail I'll schedule a code review.
Please spend some time on http://www.sql-server-performance.com/ and you can start with
- http://www.sql-server-performance.com/2007/optimizing-indexes-general/ - http://www.sql-server-performance.com/2007/clustered-indexes/
For most reports - look into covering indexes but dont weka too many because they will slow down "writes"
OK
Thats that ....
I have inboxed my CV if you feel like sending some money my way (Christmas Imefika bana)
On the CV..........am looking forward to receiving it.
oh .. the issue is not the network ..
mmmmh....
On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On Thu, Dec 1, 2011 at 6:16 PM, muskiv <kulebak@gmail.com> wrote:
Bw Liko the Mayor....at least I enjoyed reading the below.
Thanks for the useful tips.
I enjoy reading the MSDN links and I'll surely look up the ones below.
See my responses below in red?
Cheers
On Thu, Dec 1, 2011 at 7:56 AM, Agosta Liko <agostal@gmail.com> wrote:
Hey
Here is a good checklist
a. Check MaxMem for SQL Server - most times, SQL will only utilize 3GB mem even though the server has 40GB. You need to set it to use lets say 32GB so that when the instance starts, it grabs 32GB
Assuming the code is the issue....how does an over allocation of memory help (unless B below answers it?)
The idea is you have a server with 40GB .... and it is a dedicated database - it makes sense to ensure the database is using more than 3GB, juu you have memory sitting there doing nothing and during reports instead of memory being used, disk (much slower than RAM) is being used ... Its like driving a Ferari at 20kph on the Autobahn ... :)
b. If you have big tables that you hit often, you can use DBCC pintable so that the table it pinned onto memory and you dont have IO expenses when you are getting data. This means that if you have 32GB RAM and the database is 10GB, you can pin ALL the table on RAM and speed up stuff by lets say 500% on the IO side...
(http://msdn.microsoft.com/en-us/library/aa258284%28v=sql.80%29.aspx)
Note: I have not used this feature in SQL 2005 and SQL 2008 but it was pretty popluar in high traffic environment
c. Please ran a DBCC checkdb - this will ensure that there is not corruption on the db, tables, indexes etc. This is usually a nice starting point with SQL Server
Sawa
http://msdn.microsoft.com/en-us/library/ms176064.aspx
d. Look into DBCC dbreindex to rebuild your indexes - this should be ran like daily ...
http://msdn.microsoft.com/en-us/library/ms181671.aspx
Rebuilding of indexes on a daily basis. Don't you think this would be too
much? Takes quite some time to rebuild these. Rebuilding is being done once a month tho.
On a 2TB database - but that was SQL 2005, this would take 30 minutes ... if your DB is less than 100GB, it will be pretty fast - but do it huko midnight daily ...
Some more thoughts.
You issue as described is most likely because of the reports which usually have to read all the data. I would review the SQL in the reports and ensure I have the right indexes so that I am not scanning a whole table to find a record. Usually, when searching for a record, if there are no indexes, the database engine has to do a table scan... that means if you have 1 million records it will have to read all ... if you put indexes, they the query does not have to do a scan and it finds the data faster.
Understood . I think after all remedial measures fail I'll schedule a code review.
Please spend some time on http://www.sql-server-performance.com/ and you can start with
- http://www.sql-server-performance.com/2007/optimizing-indexes-general/ - http://www.sql-server-performance.com/2007/clustered-indexes/
For most reports - look into covering indexes but dont weka too many because they will slow down "writes"
OK
Thats that ....
I have inboxed my CV if you feel like sending some money my way (Christmas Imefika bana)
On the CV..........am looking forward to receiving it.
oh .. the issue is not the network ..
mmmmh....
On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
participants (10)
-
Agosta Liko -
aki -
geoffrey gitagia -
kevin karanja -
Kimani : TransTech " Love For Tech Stuff" -
Mr. Lawi -
muskiv -
Peter Karunyu -
Steve Muchai -
Steve Obbayi