| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2012-06-07 : 20:46:03
|
| so i have a database that is about 180 M rows, that we have a website that does order processing off of (users punch in a query, site outputs data and ships it) to prevent locking (which i was running into) issues, i decided to setup a second instance of sql server, and then inside that instance setup views to the database on the original sql instance. since i did that, the server seems fairly laggy, would the views cause huge performance hits, even though the database is still on the same server? is there a better way i could be doing this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-07 : 22:24:15
|
| you want to sync up between two servers? if yes, replication might be what you're looking at------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-06-07 : 22:31:04
|
| it's more the same server, two instances. so why have the data stored twice? (hence why i used views) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-07 : 22:37:13
|
| you mean two dbs? then why you need views and separate dbs? why cant you use snapshot isolation mode to avoid locking issues?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-06-07 : 23:58:59
|
| no, i actually mean two instances... on that machine i have 2 instances of sql:SERVER\MSSQLSERVER\ORDERPROCESSSINGMSSQL does the brunt of the work, but orderprocessing has views tied back to MSSQL and does the order processing... i was thinking of doing it that way so that activity on one instance wouldnt mess with another. just seems like it may be affecting performance. not sure though. so i was curious what the pros though. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-08 : 00:09:40
|
| why cant you keep them in same server itself then?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-06-08 : 01:03:33
|
| because i'm not as good at this as you :) like i said, seems like i get a lot of locking that way... i know some of it is bad code on my part because of some scripts i have (select into quries) so i was thinking a new instance would be a good way to isolate the locking. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-06-08 : 01:42:22
|
| anything you can recommend? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-08 : 11:58:23
|
quote:
Originally posted by albertkohl
because i'm not as good at this as you :) like i said, seems like i get a lot of locking that way... i know some of it is bad code on my part because of some scripts i have (select into quries) so i was thinking a new instance would be a good way to isolate the locking.
thats where SNAPSHOT isolation mode will come handy. Once you enable that by means of versioning concept it will avoid locking between concurrent read and write operationsseehttp://visakhm.blogspot.com/2010/02/avoiding-deadlocks-using-new.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-06-08 : 14:55:17
|
| i'll play with it, thanks! |
|
|
|
|
sql instances and views