Search Results

Search found 105847 results on 4234 pages for 'sql server performance'.

Page 199/4234 | < Previous Page | 195 196 197 198 199 200 201 202 203 204 205 206  | Next Page >

  • Performance triage

    - by Dave
    Folks often ask me how to approach a suspected performance issue. My personal strategy is informed by the fact that I work on concurrency issues. (When you have a hammer everything looks like a nail, but I'll try to keep this general). A good starting point is to ask yourself if the observed performance matches your expectations. Expectations might be derived from known system performance limits, prototypes, and other software or environments that are comparable to your particular system-under-test. Some simple comparisons and microbenchmarks can be useful at this stage. It's also useful to write some very simple programs to validate some of the reported or expected system limits. Can that disk controller really tolerate and sustain 500 reads per second? To reduce the number of confounding factors it's better to try to answer that question with a very simple targeted program. And finally, nothing beats having familiarity with the technologies that underlying your particular layer. On the topic of confounding factors, as our technology stacks become deeper and less transparent, we often find our own technology working against us in some unexpected way to choke performance rather than simply running into some fundamental system limit. A good example is the warm-up time needed by just-in-time compilers in Java Virtual Machines. I won't delve too far into that particular hole except to say that it's rare to find good benchmarks and methodology for java code. Another example is power management on x86. Power management is great, but it can take a while for the CPUs to throttle up from low(er) frequencies to full throttle. And while I love "turbo" mode, it makes benchmarking applications with multiple threads a chore as you have to remember to turn it off and then back on otherwise short single-threaded runs may look abnormally fast compared to runs with higher thread counts. In general for performance characterization I disable turbo mode and fix the power governor at "performance" state. Another source of complexity is the scheduler, which I've discussed in prior blog entries. Lets say I have a running application and I want to better understand its behavior and performance. We'll presume it's warmed up, is under load, and is an execution mode representative of what we think the norm would be. It should be in steady-state, if a steady-state mode even exists. On Solaris the very first thing I'll do is take a set of "pstack" samples. Pstack briefly stops the process and walks each of the stacks, reporting symbolic information (if available) for each frame. For Java, pstack has been augmented to understand java frames, and even report inlining. A few pstack samples can provide powerful insight into what's actually going on inside the program. You'll be able to see calling patterns, which threads are blocked on what system calls or synchronization constructs, memory allocation, etc. If your code is CPU-bound then you'll get a good sense where the cycles are being spent. (I should caution that normal C/C++ inlining can diffuse an otherwise "hot" method into other methods. This is a rare instance where pstack sampling might not immediately point to the key problem). At this point you'll need to reconcile what you're seeing with pstack and your mental model of what you think the program should be doing. They're often rather different. And generally if there's a key performance issue, you'll spot it with a moderate number of samples. I'll also use OS-level observability tools to lock for the existence of bottlenecks where threads contend for locks; other situations where threads are blocked; and the distribution of threads over the system. On Solaris some good tools are mpstat and too a lesser degree, vmstat. Try running "mpstat -a 5" in one window while the application program runs concurrently. One key measure is the voluntary context switch rate "vctx" or "csw" which reflects threads descheduling themselves. It's also good to look at the user; system; and idle CPU percentages. This can give a broad but useful understanding if your threads are mostly parked or mostly running. For instance if your program makes heavy use of malloc/free, then it might be the case you're contending on the central malloc lock in the default allocator. In that case you'd see malloc calling lock in the stack traces, observe a high csw/vctx rate as threads block for the malloc lock, and your "usr" time would be less than expected. Solaris dtrace is a wonderful and invaluable performance tool as well, but in a sense you have to frame and articulate a meaningful and specific question to get a useful answer, so I tend not to use it for first-order screening of problems. It's also most effective for OS and software-level performance issues as opposed to HW-level issues. For that reason I recommend mpstat & pstack as my the 1st step in performance triage. If some other OS-level issue is evident then it's good to switch to dtrace to drill more deeply into the problem. Only after I've ruled out OS-level issues do I switch to using hardware performance counters to look for architectural impediments.

    Read the article

  • Transferring an SQL Processor License to a virtual hosted environment

    - by Andrew Shepherd
    My company is currently hosting a service in-house, and we want to move to an externally hosted environment. We would then be using a virtual server. I understand that this might be spread across multiple machines, but from my perspective as a customer, this layer is abstracted away - I shouldn't know or care about the hardware that the OS is hosted on. We have a licensed edition of SQL Server 2008. This is one Processor license. Will it be a violation of the licensing agreement to use this in a virtual environment. From the reference guide here it says When licensed Per Processor With Workgroup, Web, and Standard editions, for each server to which you have assigned the required number of per processor licenses, you may run, at any one time, any number of instances of the server software in physical and virtual operating system environments on the licensed server. However, the total number of physical and virtual processors used by those operating system environments cannot exceed the number of software licenses assigned to that server For enterprise edition there is an added option: if all physical processors in a machine have been licensed, then you may run unlimited instances of SQL server 2008 in one physical and an unlimited number of virtual operating environments on that same machine. I'm having trouble getting my head around this. Would I theoretically have to get a license for every processor in this virtual environment (which is effectively impossible because I have no way of knowing how many processors there actually are)? Or can I just say that it's hosted on one "virtual" server, so that's OK?

    Read the article

  • Nested Entities and calculation on leaf entity property - SQL or NoSQL approach

    - by Chandu
    I am working on a hobby project called Menu/Recipe Management. This is how my entities and their relations look like. A Nutrient has properties Code and Value An Ingredient has a collection of Nutrients A Recipe has a Collection of Ingredients and occasionally can have a collection of other recipes A Meal has a Collection of Recipes and Ingredients A Menu has a Collection of Meals The relations can be depicted as In one of the pages, for a selected menu I need to display the effective nutrients information calculated based on its constituents (Meals, Recipes, Ingredients and the corresponding nutrients). As of now am using SQL Server to store the data and I am navigating the chain from my C# code, starting from each meal of the menu and then aggregating the nutrient values. I think this is not an efficient way as this calculation is being done every time the page is requested and the constituents change occasionally. I was thinking about a having a background service that maintains a table called MenuNutrients ({MenuId, NutrientId, Value}) and will populate/update this table with the effective nutrients when any of the component (Meal, Recipe, Ingredient) changes. I feel that a GraphDB would be a good fit for this requirement, but my exposure to NoSQL is limited. I want to know what are the alternative solutions/approaches to this requirement of displaying the nutrients of a given menu. Hope my description of the scenario is clear.

    Read the article

  • SQL Server 2012 Service Pack 2 Cumulative Update #1 is available!

    - by AaronBertrand
    The SQL Server team has released SQL Server 2012 SP2 Cumulative Update #1. This cumulative updates Service Pack 2 to include the fixes from SP1 CU#10 and a few from CU#11, including the fix for the online index rebuild corruption issue I discussed recently on SQLPerformance.com . It also marks the first time in the SQL Server 2012 timeframe that both cumulative update branches are on roughly the same schedule, which makes many of us happy I'm sure. :-) KB Article: KB #2976982 Build # is 11.0.5532...(read more)

    Read the article

  • Is there any way to send a column value from outer query to inner sub query? [closed]

    - by chetan
    'Discussions' table schema title description desid replyto upvote downvote views browser used a1 none 1 1 12 - bad topic b2 a1 2 3 14 sql database a3 none 4 5 34 - crome b4 a3 3 4 12 The above table has two types of content types Main Topics and Comments. Unique content identifier 'desid' used to identify that its a main topic or a comment. 'desid' starts with 'a' for Main Topic and for comment 'desid' starts with 'b'. For comment 'replyto' is the 'desid' of main topic to which this comment is associated. I like to find out the list of the top main topics that are arranged on the basis of (upvote+downvote+visits+number of comments to it) addition. The following query gives top topics list in order of (upvote+downvote+visits) select * with highest number of upvote+downvote+views by query "select * from [DB_user1212].[dbo].[discussions] where desid like 'a%' order by (upvote+downvote+visited) desc For (comments+upvote+downvote+views ) I tried select * from [DB_user1212].[dbo].[discussions] where desid like 'a%' order by ((select count(*) from [DB_user1212].[dbo].[discussions] where replyto = desid )+upvote+downvote+visited) desc but it didn't work because its not possible to send desid from outer query to inner subquery. How to solve this? Please note that I want solution in query language only.

    Read the article

  • Windows Azure Server Supported by RealCloud Pro

    CopperEgg CEO Scott Johnson commented on the new release: RevealCloud Pro is quickly gaining traction with our customers deploying and managing services in the cloud, virtual environments and data centers. The addition of server monitoring for Windows Azure addresses the needs of developers building services on the Azure platform as well as hybrid environments, who are looking for a robust server monitoring solution that is quickly deployed, easy to use and provides instant insight into server health. The key benefits provided by RevealCloud Pro's server monitoring begin with its flexibilit...

    Read the article

  • SQL - an error occurred during the pre-login handshake

    - by Rivka
    Until yesterday evening, I was able to connect to my server from my local machine. Now, I get the following error: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.) (.Net SqlClient Data Provider) Note, I can log on to the actual server with no problem. Yesterday, I installed IIS on my machine and set up a site using my IP address - don't know if this has anything to do. I did come across this article, followed the steps, but didn't seem to help. http://www.escapekeys.com/blog/index.cfm/2011/1/26/Microsoft-SQL-Server-Error-64-A-connection-was-successfully-established-with-the-server I also went through the following article, changed TC/IP settings, restarted, but nothing. http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/ Started trying suggestions from comments too but stopped when I realized I might be messing things up more. So, why is this happening / how can I fix?

    Read the article

  • Most common parts of a SELECT SQL query?

    - by jnrbsn
    I'm writing a function that generates a SELECT SQL query. (I'm not looking for a tool that already does this.) My function currently takes the following arguments which correspond to different parts of the SELECT query (the base table name is already known): where order fields joins group limit All of these arguments will be optional so that the function generates something like this by default: SELECT * FROM `table_name` I want to order the arguments so that the most often used parts of a SELECT query are first. That way the average call to the function will use as few of the arguments as possible rather than passing a null value or something like that to skip an argument. For example, if someone wanted to use the 1st and 3rd arguments but not the rest, they might have to pass a null value as the 2nd argument in order to skip it. So, for general purpose use, how should I order the arguments? Edit: To be more precise, out of the query parts I listed above, what is the order from most used to least used? Also, I'm not looking for solutions that allow me to not have to specify the order. Edit #2: The "fields" argument will default to "*" (i.e all fields/columns).

    Read the article

  • Replacing old server with new but different hostname & same IP

    - by MaxFr
    We have an old server 2000 and lots of users point to it by hostname and by its IP e.g \\server1, we have a new server 2008 R2 with new name serverlocation1 All folders and data have been sync'toy'd from the old server1 across to serverlocation1 each evening, the new server tree is exactly the same on the new server and all perms are correct. We need to make the new server the same IP as the old server1 as too many people access it directly via IP, and to ensure file and folder paths stay ok, anything \\server1\folder\file needs to go to \\newserverlocation1\folder\file etc I can change the old server1 name to server1-old & change IP and assign the old IP to the new server, but how do I force anything coming in for \\server1 to go to the new server and ensure anything referenced to the old server goes onto the new server ? I can CNAME but I need the new server to have the IP of the previous server so not sure if its straight forward etc..... point server1 to newserverlocation1 Then probably change DNS so that the OLD IP points to new hostname..... Any clarification appreciated.

    Read the article

  • How to setup users for desktop app with SQL Azure as backend?

    - by Manuel
    I'm considering SQL Azure as DB for a new application I'm developing. The reason I want to go with Azure is because I don't want to have to maintain yet another database(s) and I want my users to be able to access the data from anywhere. The problem is that I'm not clear regarding how to users will connect. The application is a basic CRUD type of windows app. I've read that you need to add your IP to SQL Azure's firewall to connect to it, but I don't know if it's only for administration purposes only. Can anyone clarify if anyone (anywhere) can access the data with the proper credentials? Which of the following scenarios would work best (if at all)? A) Add each user to SQL Azure and have the app connect directly to Azure as if it was connecting to SQL Server B) Add an anonymous user SQL Azure and pass the real user's password/hash with every call so the Azure database can service the requests accordingly. C) Put a WCF service in between so that it handles the authentication stuff. The service will only serve the appropriate information to the user given his/her authentication and SQL Azure would be open to the service exclusively. D) - ideas are welcomed - This is confusing because all Azure examples I see are for websites. I have a hard time believing SQL Azure wouldn't handle the case of desktop apps connecting to it. So what's the best practice?

    Read the article

  • cannot get mssql working with sql server 2005

    - by Ryan
    I'm a MySQL/Apache user, trying my hand with IIS and SQL server, so please, if this is a stupid question have patience. I'm using IIS version 7.5. PHP version 5.3.13 and SQL server 2005 IIS is running on port 90, not sure if that will make a difference or not. I know my sql server is running because I can explore/connect to it in Server management studio. I know php is configured properly, because //localhost:90/phpinfo.php works fine. I updated the php_msql.dll extension in phpinfo to: extension=ext/php_msql.dll EDIT- However, when I run phpinfo() under the "configure command" row, this is present: --without-mssql I found/downloaded the ntwdblib.dll and placed it in both sys32 and php root. All these things were supposed to fix the issue, and they haven't. This is the code I'm using, straight from php.net: <?php // Server in the this format: <computer>\<instance name> or // <server>,<port> when using a non default port number $server = 'localhost'; // Connect to MSSQL $link = mssql_connect($server, 'uname', 'pwd'); if (!$link) { die('Something went wrong while connecting to MSSQL'); } ?> obviously I'm using a real username and password, but when I load the file in my browser, I receive a 500 error. Upon checking the log, this is what is displayed: 2012-06-25 12:41:29 ::1 GET /test.php - 90 - ::1 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/536.5+(KHTML,+like+Gecko)+Chrome/19.0.1084.56+Safari/536.5 500 0 0 5 That (to me) doesn't help me much. What am I doing wrong? Thank you

    Read the article

  • How come Core i7 (desktop) dominates Xeon (server)?

    - by grant tailor
    I have been using this performance benchmark results to select what CPUs to use on my web server and to my surprise, looks like Core i7 CPUs dominates the list pushing Xeon CPUs into the bush. Why is this? Why is Intel making the Core i7 perform better than the Xeon. Are Desktop CPUs supposed to perform better than server grade Xeon CPUs? I really don't get this and will like to know what you think or why this is so. Also I am thinking about getting a new web server and thinking between the i7-2600 VS the Xeon E3-1245. The i7-2600 is higher up in the performance benchmark but I am thinking the Xeon E3-1245 is server grade. What do you guys think? Should I go for the i7-2600? Or is the Xeon E3-1245 a server grade CPU for a reason?

    Read the article

  • SQL RDBMS : one query or multiple calls

    - by None None
    After looking around the internet, I decided to create DAOs that returned objects (POJOs) to the calling business logic function/method. For example: a Customer object with a Address reference would be split in the RDBMS into two tables; Customer and ADDRESS. The CustomerDAO would be in charge of joining the data from the two tables and create both an Address POJO and Customer POJO adding the address to the customer object. Finally return the fulll Customer POJO. Simple, however, now i am at a point where i need to join three or four tables and each representing an attribute or list of attributes for the resulting POJO. The sql will include a group by but i will still result with multiple rows for the same pojo, because some of the tables are joining a one to many relationship. My app code will now have to loop through all the rows trying to figure out if the rows are the same with different attributes or if the record should be a new POJO. Should I continue to create my daos using this technique or break up my Pojo creation into multiple db calls to make the code easier to understand and maintain?

    Read the article

  • How come i7 (desktop) dominates Xeon (server)?

    - by grant tailor
    I have been using this performance benchmark results http://www.cpubenchmark.net/high_end_cpus.html to select what CPUs to use on my web server and to my surprise...looks like i7 CPUs dominates the list pushing Xeon CPUs into the bush. Why is this? Why is Intel making the i7 perform better than the Xeon. Are Desktop CPUs supposed to perform better than server grade Xeon CPUs? I really don't get this and will like to know what you think or why this is so. Also i am thinking about getting a new web server and thinking between the i7-2600 VS the Xeon E3-1245. The i7-2600 is higher up in the performance benchmark but i am thinking the Xeon E3-1245 is server grade...so what do you guys think? Should i go for the i7-2600? Or is the Xeon E3-1245 a server grade CPU for a reason?

    Read the article

  • Retrieving varbinary output from a query in sql server into classic ASP

    - by user303526
    Hi, I'm trying to retrieve a varbinary output value from a query running on SQL Server 2005 into Classic ASP. The ASP execution just fails when it comes to part of code that is simply taking a varbinary output into a string. So I guess we gotta handle it some other way. Actually, I'm trying to set (sp_setapprole) and unset (sp_unsetapprole) application roles for a database connection. First I'd set the approle, then I'd run my required queries and finally unset the approle. During unsetting is when I need the cookie (varbinary) value in my ASP code so that I can create a query like 'exec sp_unsetapprole @cookie'. Well at this stage, I don't have the cookie (varbinary) value. The reason I'm doing this is I used to get 'sp_setapprole was not invoked correctly' error when trying to set app roles. I've disabled pooling by appending 'OLE DB Services = -2;Pooling=False' into my connection string. I know pooling helps performance wise but here I'm facing big problems. Please help me out to retrieve a varbinary value into an classic ASP file or suggest a way to set & unset app roles. Either way solutions are appreciated. Thanks, Nandagopal

    Read the article

  • Return if remote stored procedure fails

    - by njk
    I am in the process of creating a stored procedure. This stored procedure runs local as well as external stored procedures. For simplicity, I'll call the local server [LOCAL] and the remote server [REMOTE]. USE [LOCAL] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[monthlyRollUp] AS SET NOCOUNT, XACT_ABORT ON BEGIN TRY EXEC [REOMTE].[DB].[table].[sp] --This transaction should only begin if the remote procedure does not fail BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp1] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp2] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp3] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp4] COMMIT END TRY BEGIN CATCH -- Insert error into log table INSERT INTO [dbo].[log_table] (stamp, errorNumber, errorSeverity, errorState, errorProcedure, errorLine, errorMessage) SELECT GETDATE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() END CATCH GO When using a transaction on the remote procedure, it throws this error: OLE DB provider ... returned message "The partner transaction manager has disabled its support for remote/network transactions.". I get that I'm unable to run a transaction locally for a remote procedure. How can I ensure that the this procedure will exit and rollback if any part of the procedure fails?

    Read the article

  • Oracle performance problem

    - by jreid42
    We are using an Oracle 11G machine that is very powerful; has redundant storage etc. It's a beast from what I have been told. We just got this DB for a tool that when I first came on as a coop had like 20 people using, now its upwards of 150 people. I am the only one working on it :( We currently have a system in place that distributes PERL scripts across our entire data center essentially giving us a sort of "grid" computing power. The Perl scripts run a sort of simulation and report back the results to the database. They do selects / inserts. The load is not very high for each script but it could be happening across 20-50 systems at the same time. We then have multiple data centers and users all hitting the same database with this same approach. Our main problem with this is that our database is getting overloaded with connections and having to drop some. We sometimes have upwards of 500 connections. These are old perl scripts and they do not handle this well. Essentially they fail and the results are lost. I would rather avoid having to rewrite a lot of these as they are poorly written, and are a headache to even look at. The database itself is not overloaded, just the connection overhead is too high. We open a connection, make a quick query and then drop the connection. Very short connections but many of them. The database team has basically said we need to lower the number of connections or they are going to ignore us. Because this is distributed across our farm we cant implement persistent connections. I do this with our webserver; but its on a fixed system. The other ones are perl scripts that get opened and closed by the distribution tool and thus arent always running. What would be my best approach to resolving this issue? The scripts themselves can wait for a connection to be open. They do not need to act immediately. Some sort of queing system? I've been suggested to set up a few instances of a tool called "SQL Relay". Maybe one in each data center. How reliable is this tool? How good is this approach? Would it work for what we need? We could have one for each data center and relay requests through it to our main database, keeping a pipeline of open persistent connections? Does this make sense? Is there any other suggestions you can make? Any ideas? Any help would be greatly appreciated. Sadly I am just a coop student working for a very big company and somehow all of this has landed all on my shoulders (there is literally nobody to ask for help; its a hardware company, everybody is hardware engineers, and the database team is useless and in India) and I am quite lost as what the best approach would be? I am extremely overworked and this problem is interfering with on going progress and basically needs to be resolved as quickly as possible; preferably without rewriting the whole system, purchasing hardware (not gonna happen), or shooting myself in the foot. HELP LOL!

    Read the article

  • Login failed for user 'DOMAIN\MACHINENAME$'

    - by sah302
    I know this is almost duplicate of : http://stackoverflow.com/questions/1269706/the-error-login-failed-for-user-nt-authority-iusr-in-asp-net-and-sql-server-2 and http://stackoverflow.com/questions/97594/login-failed-for-user-username-system-data-sqlclient-sqlexception-with-linq-i but some things don't add up compared to other appliations on my server and I am not sure why. Boxes being used: Web Box SQL Box SQL Test Box My Application: I've got aASP.NET Web Application, which references a class library that uses LINQ-to-SQL. Connection string set up properly in the class library. As per http://stackoverflow.com/questions/97594/login-failed-for-user-username-system-data-sqlclient-sqlexception-with-linq-i I also added this connection string to the Web Application. The connection string uses SQL credentials as so (in both web app and class library): <add name="Namespace.My.MySettings.ConnectionStringProduction" connectionString="Data Source=(SQL Test Box);Initial Catalog=(db name);Persist Security Info=True;User ID=ID;Password=Password" providerName="System.Data.SqlClient" /> This connection confirmed as working via adding it to server explorer. This is the connection string my .dbml file is using. The problem: I get the following error: System.Data.SqlClient.SqlException: Login failed for user 'DOMAIN\MACHINENAME$'. Now referencing this http://stackoverflow.com/questions/1269706/the-error-login-failed-for-user-nt-authority-iusr-in-asp-net-and-sql-server-2 it says that's really the local network service and using any other non-domain name will not work. But I am confused because I've checked both SQL Box and SQL Test Box SQL Management Studio and both have NT AUTHORITY/NETWORK SERVICE under Security - Logins, at the database level, that isn't listed under Security - Users, but at the database level Security - Users I have the user displayed in the connection string. At NTFS level on web server, the permissions have NETWORK SERVICE has full control. The reason why I am confused is because I have many other web applications on my Web Server, that reference databases on both SQL Box and SQL Test Box, and they all work. But I cannot find a difference between them and my current application, other than I am using a class library. Will that matter? Checking NTFS permissions, setup of Security Logins at the server and databases levels, connection string and method of connecting (SQL Server credentials), and IIS application pool and other folder options, are all the same. Why do these applications work without adding the machinename$ to the permissions of either of my SQL boxes? But that is what the one link is telling me to do to fix this problem.

    Read the article

  • Migrating SQL Server Databases – The DBA’s Checklist (Part 1)

    - by Sadequl Hussain
    It is a fact of life: SQL Server databases change homes. They move from one instance to another, from one version to the next, from old servers to new ones.  They move around as an organisation’s data grows, applications are enhanced or new versions of the database software are released. If not anything else, servers become old and unreliable and databases eventually need to find a new home. Consider the following scenarios: 1.     A new  database application is rolled out in a production server from the development or test environment 2.     A copy of the production database needs to be installed in a test server for troubleshooting purposes 3.     A copy of the development database is regularly refreshed in a test server during the system development life cycle 4.     A SQL Server is upgraded to a newer version. This can be an in-place upgrade or a side-by-side migration 5.     One or more databases need to be moved between different instances as part of a consolidation strategy. The instances can be running the same or different version of SQL Server 6.     A database has to be restored from a backup file provided by a third party application vendor 7.     A backup of the database is restored in the same or different instance for disaster recovery 8.     A database needs to be migrated within the same instance: a.     Files are moved from direct attached storage to storage area network b.    The same database is copied under a different name for another application Migrating SQL Server database applications is a complex topic in itself. There are a number of components that can be involved: jobs, DTS or SSIS packages, logins or linked servers are only few pieces of the puzzle. However, in this article we will focus only on the central part of migration: the installation of the database itself. Unless it is an in-place upgrade, typically the database is taken from a source server and installed in a destination instance.  Most of the time, a full backup file is used for the rollout. The backup file is either provided to the DBA or the DBA takes the backup and restores it in the target server. Sometimes the database is detached from the source and the files are copied to and attached in the destination. Regardless of the method of copying, moving, refreshing, restoring or upgrading the physical database, there are a number of steps the DBA should follow before and after it has been installed in the destination. It is these post database installation steps we are going to discuss below. Some of these steps apply in almost every scenario described above while some will depend on the type of objects contained within the database.  Also, the principles hold regardless of the number of databases involved. Step 1:  Make a copy of data and log files when attaching and detaching When detaching and attaching databases, ensure you have made copies of the data and log files if the destination is running a newer version of SQL Server. This is because once attached to a newer version, the database cannot be detached and attached back to an older version. Trying to do so will give you a message like the following: Server: Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 6, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Connection Broken If you try to backup the attached database and restore it in the source, it will still fail. Similarly, if you are restoring the database in a newer version, it cannot be backed up or detached and put back in an older version of SQL. Unlike detach and attach method though, you do not lose the backup file or the original database here. When detaching and attaching a database, it is important you keep all the log files available along with the data files. It is possible to attach a database without a log file and SQL Server can be instructed to create a new log file, however this does not work if the database was detached when the primary file group was read-only. You will need all the log files in such cases. Step 2: Change database compatibility level Once the database has been restored or attached to a newer version of SQL Server, change the database compatibility level to reflect the newer version unless there is a compelling reason not to do so. When attaching or restoring from a previous version of SQL, the database retains the older version’s compatibility level.  The only time you would want to keep a database with an older compatibility level is when the code within your database is no longer supported by SQL Server. For example, outer joins with *= or the =* operators were still possible in SQL 2000 (with a warning message), but not in SQL 2005 anymore. If your stored procedures or triggers are using this form of join, you would want to keep the database with an older compatibility level.  For a list of compatibility issues between older and newer versions of SQL Server databases, refer to the Books Online under the sp_dbcmptlevel topic. Application developers and architects can help you in deciding whether you should change the compatibility level or not. You can always change the compatibility mode from the newest to an older version if necessary. To change the compatibility level, you can either use the database’s property from the SQL Server Management Studio or use the sp_dbcmptlevel stored procedure.   Bear in mind that you cannot run the built-in reports for databases from SQL Server Management Studio if you keep the database with an older compatibility level. The following figure shows the error message I received when trying to run the “Disk Usage by Top Tables” report against a database. This database was hosted in a SQL Server 2005 system and still had a compatibility mode 80 (SQL 2000).     Continues…

    Read the article

  • Getting results in a result set from dynamic SQL in Oracle

    - by msorens
    This question is similar to a couple others I have found on StackOverflow, but the differences are signficant enough to me to warrant a new question, so here it is: I want to obtain a result set from dynamic SQL in Oracle and then display it as a result set in a SqlDeveloper-like tool, just as if I had executed the dynamic SQL statement directly. This is straightforward in SQL Server, so to be concrete, here is an example from SQL Server that returns a result set in SQL Server Management Studio or Query Explorer: EXEC sp_executesql N'select * from countries' Or more properly: DECLARE @stmt nvarchar(100) SET @stmt = N'select * from countries' EXEC sp_executesql @stmt The question "How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?" addresses the first half of the problem--executing dynamic SQL into a cursor. The question "How to make Oracle procedure return result sets" provides a similar answer. Web search has revealed many variations of the same theme, all addressing just the first half of my question. I found this post explaining how to do it in SqlDeveloper, but that uses a bit of functionality of SqlDeveloper. I am actually using a custom query tool so I need the solution to be self-contained in the SQL code. This custom query tool similarly does not have the capability to show output of print (dbms_output.put_line) statements; it only displays result sets. Here is yet one more possible avenue using 'execute immediate...bulk collect', but this example again renders the results with a loop of dbms_output.put_line statements. This link attempts to address the topic but the question never quite got answered there either. Assuming this is possible, I will add one more condition: I would like to do this without having to define a function or procedure (due to limited DB permissions). That is, I would like to execute a self-contained PL/SQL block containing dynamic SQL and return a result set in SqlDeveloper or a similar tool. So to summarize: I want to execute an arbitrary SQL statement (hence dynamic SQL). The platform is Oracle. The solution must be a PL/SQL block with no procedures or functions. The output must be generated as a canonical result set; no print statements. The output must render as a result set in SqlDeveloper without using any SqlDeveloper special functionality. Any suggestions?

    Read the article

  • F# performance in scientific computing

    - by aaa
    hello. I am curious as to how F# performance compares to C++ performance? I asked a similar question with regards to Java, and the impression I got was that Java is not suitable for heavy numbercrunching. I have read that F# is supposed to be more scalable and more performant, but how is this real-world performance compares to C++? specific questions about current implementation are: How well does it do floating-point? Does it allow vector instructions how friendly is it towards optimizing compilers? How big a memory foot print does it have? Does it allow fine-grained control over memory locality? does it have capacity for distributed memory processors, for example Cray? what features does it have that may be of interest to computational science where heavy number processing is involved? Are there actual scientific computing implementations that use it? Thanks

    Read the article

  • Troubleshooting source of heavy resource-usage on a server 2008 running multiple sites

    - by batman_man
    Hi, I am running about 10 asp.net websites on a hosted virtual server. The server runs Server 2008 - each website is backed by its own database running on SQL server 2008 on the same box. Lately the box has seemed really slow. The only kind of discovery i could think of doing was looking in the task manager, where i can see w3wp and sqlserver.exe jumping to 40% cpu usage every 5-10 seconds. What are the steps i can take to determine which of my websites is taking these resources and or what database is getting hit the most? I have of course ssms installed on the machine as well. As you can tell, my sysadmin skills are very very limited - any help would be much appreciated.

    Read the article

  • VS2010 + Resharper 5 performance issues

    - by Jeremy Roberts
    I have been using VS2010 with Resharper 5 for several weeks and am having a performance issue. Sometimes when typing, the cursor will lag and the keystrokes won't show instantaneously. Also, scrolling will lag at times. There is a forum thread started and JetBrains has been responding. Several people (including myself) have added their voice and uploaded some performance profiles. If anyone here has has this issue, I would encourage you to visit the thread and let JetBrains know about it. Has anyone had this problem and have a suggestion to restore performance?

    Read the article

  • Measuring Web Page Performance on Client vs. Server

    - by Yaakov Ellis
    I am working with a web page (ASP.net 3.5) that is very complicated and in certain circumstances has major performance issues. It uses Ajax (through the Telerik AjaxManager) for most of its functionality. I would like to be able to measure in some way the amounts of time for the following, for each request: On client submitting request to server Client-to-Server On server initializing request On server processing request Server-to-Client Client rendering, JavaScript processing I have monitored the database traffic and cannot find any obvious culprit. On the other hand, I have a suspicion that some of the Ajax interactions are causing performance issues. However, until I have a way to track the times involved, make a baseline measurement, and measure performance as I tweak, it will be hard to work on the issue. So what is the best way to measure all of these? Is there one tool that can do it? Combination of FireBug and logging inserted into different places in the page life-cycle?

    Read the article

< Previous Page | 195 196 197 198 199 200 201 202 203 204 205 206  | Next Page >