I am debating whether to go to all the trouble of setting up on-demand Profiler traces on some test servers for the developers here. Really just tracing RPC:Completed and SQL:BatchCompleted, so the developers can at least try to catch a performance problem before going to production. The question I have, though, is just how useful is this sort of information to mid- to low-level (i.e. experience) developers. One of the bigger concerns is over Java applications, which like to hide their queries behind a lot of "sp_cursorfetch" calls.
My question to the forum is if you are a developer, have you ever dreamed of having this sort of information available? How useful is it?
I am going to try to post a poll along with this, but I am not sure it will work..
I need some help knowing what to look for in Profiler to troubleshoot an issue.
I've got an application that accesses a SQL Server database that has suddenly started timing out when users launch and attempt to log in, and I'm trying to find out where and why the application might be timing out (whether it's a server issue, a stored procedure or SQL query from the application that could be optimized, a table that could be truncated or archived, etc.). All I have to work with from troubleshooting the database side are a series of trace files from Profiler that were run for a total of about 5 minutes while the application was launched and then timed out. Of course, there are a whole lot of statements being issued, hundreds of tables being accessed, lots of stored procedures and even more ad-hoc queries coming straight from application source code.
So my question is, what do I need to look for in these trace files that might be a red flag to an issue? I'm no DBA, but I know that really long durations might be a tip-off. I'm only seeing these on the occasional Event:Audit Logout (which I read in another thread could potentially be very normal). Anything else that I might want to filter for?
I have a procedure in a history database that does insert into 3 tables inside a transaction. users complaint that the proc sometimes takes too long during heavy usage. I did some traces to see what is taking up the time, I found that the rpc duration was averaging > 500 ms (should only take 50ms). I checked to see if one of that statements were taking too much time, but only see the commit transaction statement taking around 500 ms). I check the avg disk queue to be around 30. ( this is on a single local disk) .
So is this definitely a disk issue, or is there something else I need to check
Sould one has a seperated environment for production and test system? How do you do it on a same server? Install two instance? How do you seperate test DBs from the production DBs? Please advise...Thank you
I must be missing something, and its starting to fusterate me. Bear with me here. I created a site for a ...client I guess you'd call it, and made this really slick newletter generator thing. The people from the web enter in their info, and if they want, they sign up for a newsletter -- all tied into a db, 1 table, 4 stored procedures, REALLY simple stuff. They insisted I used a certain webhost which, on paper, looks like it will fit the bill. I'm starting to question that. On top of the newsletter thing, I created an aspnetdb for the "administration" side of it for her to log into and send out the newsletter so total, there's 2 dbs in the app_data folder. Locally, it works GREAT and on my test box (iis6) that is running 2k5 express. The webhost runs sql2k in (what I consider) a bastardized way. Can't use the management studio, can't use anything except a really weak web-based interface which adds to my fusteration. Anyway, my questions : 1, is there a way to make the mdf files work with sql2k without having to re-do the whole thing and 2, if I have to redo it, does anyone have an example connection string that might help out?
I want to build a SQL testing environment in an active/active setup. Any recommendations on what I could use if I want to set up the most bare-bones system. I want to do it as cheaply as possible.
We have a production database that was generated by a vendor. The vendor wants us to test a new version of their software. This testing process will take several months. The users want the testing to be as real time as possible. I have developed a series of scripts that will back up our databases and ship them over to our test environment on a nightly basis. We also of course have nightly backups. As a general rule, we do full backups once a week and differentials on a nightly basis.
We are a phone company that has transactions being applied to the database 7 X 24.
My question is this: Is there a way (an option or something) that when my backup of the production database which is destined for the test environment runs, I can tell it to not set the flags that indicate a backup has been done. What I want to avoid is the differential backup process from being 'Confused' about what backup it is doing a differential for.
I would appreciate any help or insight you can give me.
I am try to put together options in regard to creating a test environment for our Dynamics NAV system. The environment will be mainly used to test new releases / changes ahead of applying them to production.The 2 options I am considering are…
1.Create a second Test instance on our Production SQL Server to host a test database
2.Purchase a set of SQL developer licences and having a totally separate server for our test environment.
My preference would be option 2. However I need to build a convincing case that this is the best way forward. I wondered if I could tap into the thoughts of the SQL Central community and see how other approach this.
So, we are about 3 weeks away from going into production, and somehow we failed to give much thought to deploying our RS project into production.
We have over 110 report models that need to be deployed into production, and until now, we just deploy into our dev and test environments using Visual Studio. But, in our production environment, our deployers will not have Visual Studio.
Is there any simply backup/restore method that can be used to move our test environment into production? Please don't suggest a copy of each file one at a time /sigh.
We will be implementing our first SQL cluster in December. Our current plan calls for a shared development/test database server with one physical server, but two SQL Server instances. Our production environment will be a SQL cluster. Is it necessary to create a clustered test environment for testing patches, hot-fixes, etc...?
Setting up Transaction Replication in test environment. I am willing to bet that most of you take a production backup (if so, how, and using what?), restoring the database to your test environment, then running a snapshot to your subscriber and away you go.
But perhaps you take a backup of your publisher and subscriber, if so, how do you know there are no inconsistences because there were transactions sitting on the distributor?
What do you do if you have additional indexes on the subscriber for reporting, that are not on the publisher?
Here at work we are having issues with getting consistent databases set up with T Rep, missing rows, duplicate keys at subscriber etc. How to avoid these issues.
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
Any suggestions on how I can monitor the following without using traces? I am a dba/developer working as a developer on a contract, and I'm supposed to be tuning. However, I can't run traces. I've got my own procs that monitor locking, etc. But I would like to get at least i/o and cpu throughout the day. It would also be nice to get the query executed. Basically, the type of stuff you'd normally use traces for.
I know about @@cpu, @@io etc., but these are basically useless (no?) since they only record since the server was started. There is a stored proc but it only monitors these things since the last time it was run.
Does anyone know how I could utilize the above? I tried to write a script but I couldn't get it to work. :(
I realize that in general this is a ridiculous request, but I thought I would ask anyway.
This one has stumped me. Hopefully somebody can help. A while ago, I setup a trace that posted the log to the desktop. I needed to stop the trace this morning, so I went into the profiler and deleted the traces. There was a private and shared trace. Now every time I start up something that has to do with sql server, the log pops up on the desktop. I'm not sure why the trace wasn't deleted or stopped. The trace includes what program accessed sql. Whether it is EM or Query analyzer or ISQL. It gets posted in the log. Any suggestions? I need to remove this because the log fills up the drive and causes the server to crash.
I want to use ShowPlanXML in my traces. Information witch is given by this event is quite interesting but I dunno how to bound it with other events. For example I have SqlBatchComplete event and want to get XML plan of it but I have no idea how to do it.
When I restart the server that hosts the database engine SQL Server 2005 Standard Edition SP4, the trace gets up mytrace-5.trc with a size of 100 MB and id is 2, leaving the hard disk space, then the SQL Server stops execution of the trace due to lack of space. I do not know how to erase the trace, because I do not know where is it.
The archive log looks like : SQL Trace ID 2 was started by login "sa"
Trace ID '2' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.
We are setting up a test lab environment with 100 machines. Â We want one master testing db that gets replicated to each to run scripted application tests nightly. Â
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. Â I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
hi every one, i need to test SSIS pacakge which will import data from different database where record count is around 5 millions. iam planning to test it through c# code as well as manually also. SSIS source : consist of 7 tables SSIS destination :consist of 7 tables Using c# code iam trying to run ssis package through batch file. i am putting expected rowcount, column count in an excel file and comparing same with destination tables by writing query implementing ADO.Net concept. am i going right way ,can any one suggest best and productive way to test the ssis package . what are the other things i need to test it. do any one can add test cases to it.
S.No
Test Case
1
Verify all the tables have been imported.
2
Verify all the rows in each table have been imported.
3
Verify all the columns specified in source query for each table have been imported
4
Verify all the data has been received without any truncation for each column.
5
Verify the schema at source and destination
6
Verify the time taken /speed for data transfer
7
Fields truncated due to difference in length of the field at destination. Regards Arif shareef
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
Can only member in the System Administrators role use the SQL Profiler’s? Is there any other role or way to allow user to use this tool other then adding them to this role? Thanks you
Does anybody have any idea if there is a bug in SQL 7.0 where running multiple traces causes the SQL 7.0 to crash ?? This is running in a clustered environment.
I've looked high and low for information regarding this problem to no avail. Profiler works fine from the local server where SQL7 is running, (using NT security). But even though my client is multiprotocol, I cannot connect from my desktop using SQL security (ODBC error) or NT authentication. NT security gives me ConnectionOpenRcpBindingSetAuthInfo(). I do have a valid id on the NT machine as well and am not getting any errors in the security events log.
Can/How do you connect remotely to the profiler using TCP/IP or Mulitprotocol client?
Can anyone tell me what is a profiler in SQL server 7.0? What I exactly want to know is, in which practical situation u will find the profiler useful. Explaination with example will be appreciated.
Hi I need to track the connections hitting to my server through application???
I m doing some work in Profiler but i m not sure from where to start it..I m using SQL SERVER 2005...I m accesing few pages on application and that i want to track that activity involving at server side
I have a small doubt. where is the ideal place to run a profiler . can we run a profiler on production server or we have to run profiler on a seperate instance and connect to production server
