Tuesday, September 24, 2013

[SQL 2012] Alias for testing

[SQL 2012] Alias for testing

Alias for testing

Posted: 24 Sep 2013 03:15 AM PDT

I'm trying to make my remote development environment look very much like my client's environment. I want to set it up so when my programs seek a server called "MTF", they actually get pointed to a network server in my development environment called "LION". LION (SS2012) is up and running, and I can connect to it from my laptop using SSMS2008R2 with the server name "LION".I can't seem to connect using the server name "MTF". I've set up an alias on the server called MTF, and I've set up the local client with the "MTF" alias following instructions found here: [url]http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/[/url]Am I forgetting something?

Weird slowdown issue, possible table lock

Posted: 24 Sep 2013 12:26 AM PDT

Can anyone help me understand what is happening here? I have a tall table containing a list of IDs and a numeric value that refers to a type of demog. It's being queried through a series of subqueries that are joined together eg....[b]select distinct ID from (select subquery1.RID RID from ((Select ID from demogtable where (demogID='133766')) Subquery1 inner join (Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join (Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join (Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join (Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join (Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1[/b]This might seem like an inefficient way of doing it but due to the way the table is set up, it returns about 45000 IDs in approx 5 seconds.The problem comes when I try to use this results set as a subquery to update another table e.g....[b]update Outputtable set queryID=3483 where ID in (select distinct ID from (select subquery1.ID ID from ((Select ID from demogtable where (demogID='133766')) Subquery1 inner join (Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join (Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join (Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join (Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join (Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1) and queryid is null[/b]This query takes several hours to complete and causes the CPU to run at 99%. I can't understand why though, as the subquery, when run in isolation is so fast. The outputtable is about 50k, and has only just been created by the process, so isn't locked.Even stranger, I can make the query run fast again, if I apply a TOP to the subquery. e.g.....[b]update Outputtable set queryID=3483 where ID in (select distinct top 10000000 ID from (select subquery1.ID ID from ((Select ID from demogtable where (demogID='133766')) Subquery1 inner join (Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join (Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join (Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join (Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join (Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1) and queryid is null[/b]Someone told me that using the TOP statement can be a good trick to make SQL actualise a subquery in tempdb before attempting to move on, and that appears to be what's happening here, but I can't understand why the subquery would cause issues when run inline.If I run sp_lock when the first update statement is running, it appears that there are 4k+ locks going on.Can anyone explain this behaviour?

temp table vs permanent table performance

Posted: 24 Sep 2013 12:01 AM PDT

Hi There ,Im handling cores of data which will refreshed in every run.for this which one I can go with ? temp table or permanent table ?

Set up jobs on SQL Server 2012 Servers in AlwaysOn configuration

Posted: 24 Sep 2013 02:04 AM PDT

Hello everybody,I am a SQL DBA and need some help.We currently have two 2012 SQL Servers in High Availability.I scripted and placed the jobs on both Servers and added the 1st step to all them utilizing the following script:IF dbo.fn_hadr_is_primary_replica ('db_name') <> 1RAISERROR('This is not the preferred replica, exiting with success',11,1)So, the jobs are basically running according to their schedules on both Servers, and exiting the 1st step with Success if: dbo.fn_hadr_is_primary_replica ('db_name') <> 1It works fine. but this is not a fancy solution at all.For example, since the SSIS Packages reside in the Catalogs that I created, for executing them it does not matter from which job and on which Server they are running.Is there a way do something like that with the jobs; anotherwords set some Centralized location for them and run all the jobs from this location?Or maybe there anybody is aware of a better way to set up the jobs in AlwaysOn?Any help with this matter will be greatly appreciated.Thank you.Alex

ssis vs jobs

Posted: 24 Sep 2013 01:28 AM PDT

I just started as dba at new co. I'm looking over what the previous guy did. He has some ETL process done using SSIS. I'm looking the ssis over. it's ssis done in sql server 2008, running from sql server 2008 instance however it is pushing all of it's data t sql server 2012. I suppose he didnt want to convert it to 2012 even though the destination db was upgrated. It basically calls a few .exe's to bring down files, it then transofmes the data, loads it into sql server, runs several procs on a handful of db's, then archives the data to history tables. I don't see any parallel processing nor anything that would warrent using ssis, everything is going to one destination instance. It could easily be create within sql server as job w/ steps.Does anyone have any input on why i should keep this running as an ssis? Just curious.

activity monitor in SSMS Unable to access -- error.

Posted: 23 Sep 2013 08:15 AM PDT

I receive the following messages when trying to use activity monitor in SSMS Error:The activity monitor is unable to execute queries against server.activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the Activity Monitor.Additional Information: The RPC server is unavailable. (Exception for the HRESULT: 0x800706BA) (mscorlib)

crazy replication question

Posted: 23 Sep 2013 04:36 AM PDT

I have replication running with a 2 publications coming out of the same database. Publication number 1 has all but 300 of a 8000 table database, publication number two has only the 300 of the 8000 tables. So basically I have all the data published, i just have 300 tables subscribed to one database and the remaining 7700 subscribed to the other database.Both are transactional push. When I monitored the Log Reader Agent Status, I was very surprised to see that both publications were delivering the exact same transactions/commands which leads me to believe that with transactional replication, everything is delivered to the distributor and then from there the decision is made as to which articles are pushed to the subscriber.So in other words if I have an 8000 table, 2TB database, and I only want 1 table to be transactionally replicated. All changes for all 8000 tables will be delivered to the distributor and only the changes for the 1 table will be applied to my subscription database.Does this seem accurate?thanks!

Doubts with Log Shipping

Posted: 23 Sep 2013 09:34 PM PDT

When logshipping is configured, we know that along with LS Backup/copy and restore jobs, LS ALert jobs are also created, which alerts whenever the defined threshold for the backup or restore has crossed. I want to know where these alert messages are logged apart from msdb..
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog