Hello,
I just wanted how we can find the system performance without using tool like performance monitor or profiler.
I just need the query like equalent to peformance monitor for see the systmem status of CPU ,IO ,Memory and etc..
Thanks,
Ravi
All,
Is there a system variable to indicate a package run successfully or failed?
I have a task in Event Handlers to log the process into a table, but I still need extra information like execution status, more likely,
if package successfully executed, I want to get a specific values from SQL server (like in DTS, execution_status = 4 when success), Any suggestions are welcome.
Thanks in Advance
Is there an app similar to Perfwiz (for Exchange) that will collect the OS and App performance counters?
View 1 Replies View RelatedWhich is the best third party tool for SQL Server 2005 Performance Tuning/Optimization.
We purchased the Toad for SQL Server from Quest sofwater but it works only with dbo schemas....So if ur users were in schema xyz then it would not recognize it because they develop the tool for 2005 based on SQL Server 2000 where the schema ownesrhip is tied to the users.
Hi all,
I have downloaded an evaluation copy of a SQL Server performance tool (a fancy version of the Profiler) called Speed Coefficient from Imceda. Pretty interesting so far, but some questions are forthcoming (and probably will continue to come as I drill down and learn more about performance thangs).
It tells me I have a recompilation that I did not expect, it says the reason is "object not found at compile time, deferred to run time", but doesn't do too well at specifically telling me which object it is complaining about (yeah, not REALLY a complaint, but perhaps more a "mention", but I digress...)
I thought originally it was, perhaps, an object that I had not referenced correctly, but as it turns out, it is, I believe, referring to a global temporary table one of my procs creates. Upon further reflection/introspection, it makes sense to me that this is the case, since it won't HAVE the temp table object to kick around until it is created at run time.
Does this make sense? If so, I guess this is one of those times where the tool just makes reference to a possible issue, but it's up to the user to understand what the underlying cause of the "mention" is, and to determine if it is "OK" to have the recompilation occur.
We did configure reporting services to use a custom security extension on a Windows 2000 sp4 machine and using IIS 5.0.
The website for reporting services is not using the default website in IIS. It uses IP address : 192.168.1.20 and host header name is rpt2.compagny.com. When accessing the report manager at http://rpt2.compagny.com/reports$sql_2005 from another machine or on the machine where reporting services is installed we get an error message.
Error Message : The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
The RSWebApplication.config file looks like the following :
<UI>
<CustomAuthenticationUI>
<loginUrl>/Pages/UILogon.aspx</loginUrl>
<UseSSL>False</UseSSL>
</CustomAuthenticationUI>
<ReportServerUrl>http://server name/ReportServer$sql_2005</ReportServerUrl>
</UI>
The RSreportserver.config file looks like the following :
<UrlRoot>http://server name/ReportServer$SQL_2005</UrlRoot>
We already tried replacing the server name with the ip address in both the RSreportserver.config file and RSWebapplication.config file and it still does not work.
here is the logfile information :
aspnet_wp!ui!1!2/26/2007-14:41:51:: e ERROR: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
aspnet_wp!ui!1!2/26/2007-14:41:51:: e ERROR: HTTP status code --> 500
-------Details--------
Microsoft.SqlServer.ReportingServices2005.RSConnection+MissingEndpointException: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. ---> System.Net.WebException: The request failed with HTTP status 404: Object Not Found.
at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.ReportingServices2005.RSConnection.MissingEndpointException.ThrowIfEndpointMissing(WebException e)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
What should be the settings in IIS and the configuration files ?
Any help would be appreciated.
Chi
Hi guys , may I know is there any examples of store procedure/scripts for monitoring replication status and performance ? I just know about sp_replmonitorhelppublisher. Thx for the assistance.
From,
Hans
I am reading kb 934164. I am confused about (creating system administrator) domain user accounts....
IN SQL 2005 USER PROVISIONING Tools
under kb934164 8e type a window account by the following format
domain/user 8k Type a windows account by following format domain/user
DO I simply type domain/user or do I actually Type my domain/user account
What is domain user?
In other words where does domain (PASSWORD) come from?
where does user(PASSWORD) come from?
I have being trying to find the answer for this
Is there anything else I need to be prepared for in user
provisioning. By the way do you need to turn off uac in vista while installing
sql 2005....Thanks Is there any examples of this? I just want to get it right....
Hi,
I am developing an editorial system(MS-SQL). There are a public part(frontend for reader) and an editorial part (backend for editor, admin,...).
The public part of system will be used approximately 1000 acesses/s.
A possible solution:
I solve whether I have to use two databases:
1.DB all data
2.DB with data for frontend
acesses...............trigers
--------->2.DB <----------1.DB
or more of databases or else?
The purpose is, a reader should have good response that the reader don't wait.
Could you give me any advice ?
Thanks
I am developing a new application and am trying to find the best way to deliver XML documents to the app. Here's the scenario: each set of xml docs will have a unique identifier. The app needs to retrieve a known xml doc by using this identifier. So the dilema I am having is as follows: I could create a folder for each identifier and load the xml docs relevant to each id into the folder. For the app to retrieve the document, it would simply construct a file path and the windows B-tree search would find it and return it very quickly. Alternatively, I could create a table with a primary key that stores the unique identifier, cluster it, and store the xml in a blob field.
Does anyone have any experience, references, or thoughts about which would be the best way to do this? I am an avid database developer and the app will be using sql 2000 anyway, however it seems in this situation the database might not be the best option in terms of speed, performance, and resource useage. Thoughts?
Thanks,
Mike
Hi,I am starting a new job next week. Part of what I am required to do isa "system diagnostic" on a SQL Server 2000 box to determine what areascan use improvement - this would include configuration settings,backup/recovery, sql tuning, etc... and anything else I may not havementioned here!What I need is a thorough and systematic approach to doing this. Cananyone please give me advice, or point to a FAQ or other links thatdiscuss this. I am running out of time.THANKS MUCH
View 2 Replies View RelatedHello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedWe are in plan to build a Monitoring tool using PowerShell and Performance Monitor which could monitor 10 to 20 servers. Do you have any reference of any existing tool using Performance Monitor to monitor the SQL Server and available for free? I didn't want to put some effort, if something is available already.
View 2 Replies View RelatedI 'inherited' a group of SQL Server server class machines. They aretrue server technology but the disk sub-systems are lacking. There isone hot-swap backplane that all the drives share (with one SCSIchannel) thusly even though there are three logical drives (composedfrom 6 to 8 hard drives), they all go through one channel. This iscreating a performance issue that is noticable and can be seen invarious performance counters that Microsoft recommended one shouldmonitor in terms of disk I/O. For a cheaper 'fix', I can add aseperate two drive bay (with its own SCSI channel) with mirroreddrives. I would then mostly likely place the transaction log files onthis new channel. Or I could place the indices filegroup files onthis new channel for DBs with mainly searching going on (not muchupdating). If I went this route I would be leaning towards thetransaction log move since the second method would require me movingDBs around quite a bit. Any input on this solution (besides spendingmore money)?What I would prefer to do is get a better server class machine or addan external drive bay solution (not a SAN). I would try to get threeor four SCSI channels in the new hardware to split the differentfile/filegroups out (i.e. transaction logs files, data filegroup,indices filegroup, etc.). My only concern here is: would this moreexpensive solution be worth the money? As far as replacing servers, Ihave only two kinds of experience...replacing somewhat underpoweredservers with slightly less underpowered servers and replacing overkillservers with even more overkill servers. In both cases, the disksub-systems were fairly equivalent from the old system to the new one.Will going the three/four channel route really get data moving along?We have one server in particular that hosts a database (one of many onit) for a web application that gets decent traffic (it is a privatelogin based system for internal use and external use by our clients'agents). Periodically throughout the day, there are 2-5 minute burstswhere performance slows to a crawl. I want to spend more timeprofiling queries and such before recommending we spend more money,but the folks I am working for want quick results and there is quite abit of stored procedure logic to profile and investigate. I know thedisk sub-system is definately in need of an overhaul, but I would liketo get an idea of peformance gains from adding either one additionalchannel over the existing single channel as well as going thethree/four channel route over the existing single channel setup.Any information would be greatly appreciated.Regards,Tony
View 2 Replies View RelatedSummary: Started replication April 1 of 4M xact / day publishing system to subscribing system.
Performance was good. Latency was ~ 5-7 seconds.
May 10 we noticed that the DB was behind (latency was 12 hours).
All performance counters seem good with the exception of the disk.
. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.
. During this period, Disk % Busy (1 - Disk % Idle) is 100%
The publisher DB publishes about 50-52 xacts/sec.
Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.
I know this because several weeks ago, the network went down, we were 24 hours behind.
When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.
What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.
We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).
I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.
My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.
Thanks for any help on this!
Jack Griffith
Configuration:
Subscribing System:
SQL Server: 2000, SP4 - 8.0.2039
CPU - 2.8GHZ Xeon, Quad Dual-core
Memory - 3.5GB RAM
Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:
C: 39GB System and Programs
D: 97GB Log space
E: 982 GB Data space
Replication configuration:
- nosynch, continuous Transactional Replication
- Distribution db is on Subscription system
- distribution - Publication of approx. 50 transactions / second
Subscriber DB configuration:
DB size: 64458 MB
Logging: Simple (at this point)
distribution
DB size: 3111 MB
Logging: Simple (at this point)
I need a way to determine if a query is still running on the database server. Is there a way to do this?
If the query is still running after 2 hours I need to send a message. How can I query the database for the status of a query?
Hi,
I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).
Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?
Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants" ''' <summary> ''' The connection string for Active Directory. ''' </summary> 'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string> ''' <summary> ''' The LDAP search filter need to find a user in Active Directory. ''' </summary> 'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region ''' <summary> ''' Gets all active directory groups for the user. ''' </summary> ''' <returns>All dataset permissions for the user.</returns> <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _ Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable ' Setup the active directory search. Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING) searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName) searcher.SearchScope = SearchScope.Subtree searcher.PropertiesToLoad.Add("distinguishedname") ' Run the active directory search. Dim result As SearchResult = searcher.FindOne() Dim userEntry As DirectoryEntry = result.GetDirectoryEntry() Dim userGroups As New ArrayList GetActiveDirectoryGroupsForEntry(userEntry, userGroups) Return userGroups End Function Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars) GroupID = New SqlChars(CType(source, String)) End Sub ''' <summary> ''' Recursively gets the active directory groups for the directory entry. ''' </summary> ''' <param name="entry">The active directory entry.</param> ''' <param name="groups">The list of groups.</param> Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList) For i As Integer = 0 To entry.Properties("memberOf").Count - 1 Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString()) groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString()) GetActiveDirectoryGroupsForEntry(memberEntry, groups) Next End SubEnd Class
Is it possible to create an SSIS package that checks for a running Query on my SQL db?
I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?
I'm looking for suggestions on what Query tool to use on SQL DB's for a group
of people who are used to creating ACCESS QUERIES. I'm having trouble running SQL as the backend and ACCESS as the query tool to run ANALYSIS.
TIMEOUT issues and such. So, I thought we might as well use the SQL tools.
Any suggestions...
I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.
Thanks in Advance
Jacx
I had SQL200 query analyzer loadded on a machine but it crashed.
Culd find CD. We decided to load SQl Server Manager sudio express from a sql 2005 CD. THe problem is that wen running queries with it, it puts some strange characters in the output. They arenon-printable characters so you don't see them
when you edit them in notepad. I have an editor that allows you to edit different
modes. I looked at the file in hex mode and found the characters there at the beginning of the first record. I just want the results in the result pane of the query to save as a regular ASCII text file comma delimited. THis same script was used successfully
in SQL2000 query Analyzer. Can anyone help?
I get records from a query whose status is "Marked". I need another column to be added to result set which just says "Marked" for every record. Is that possible in query?
View 6 Replies View RelatedHi,
we have a reporting database that needs to be refreshed every week. I normally get a big backup file (50GB) from our business partner very Sunday morning; I have setup a job to restore the database once I got the backup file. The restore job normally will take about 4 or 5 hours to finish, but sometime it may take much longer (more than 10 hours). We knew there is some kind of disk storage contention going on the SAN. When this problem happens the system guy always try to pick my db restore process to be the victim. I hate to kill a job when it has 90% done, but I have no way to tell the restore progress status. Does someone know if I can query database restore progress information from any DMVs?
Thank you for your help in advance.
David Zhang
Hi !!We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets say http://salebyowner.com/advancedSearch.phpWe have few more options than this on which customer can do search. I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?
View 6 Replies View RelatedAgileInfoSoftware LLC is proud to announce AgileInfoSoftware PowerQuery*4.1 release. PowerQuery is free database query tool to work with multiple databases simultaneously and effectively.
The key features of PowerQuery 4.1 are:
One unified user interface works with*all your databases. No need to switch from one database to another.
Easy to build and execute query.
Use native database connectivity to improve database query performance.
Graphical*table edit and commit change without writing any INSERT, UPDATE or DELETE statement.
Automatically monitor Oracle Server side activities, such as executed SQL statements, server memory usage, disk usage and many other performance measurements
To obtain free PowerQuery 4.1, go to http://ww.agileinfollc.com. After download and install the software, send the registration code (32 bytes hash string generated based on your installation time) to support@agileinfollc.com, we will send back free license code to activate the software permanently.
Does anyone know of a vendor who provides a packaged application that allows a user to pass T-SQL commands from a browser, to our IIS web server, to our SQL Server database?
Thanks!!!
I'm getting this error in a simple DTS package.
No query specification returned by transform status.
Any ideas what's causing this ?
DTS is simply running a query (which returns data via preview)
and insert results into a table.
Thanks
Mark
Just a general question about any recommendations for a third party tool that would allow setting up a data-dictionary against SQL Server tables and a user could go in and build their own queries, reports. Perhaps they could save their query/report definitions for reuse. Other nice to haves - ability to output to PDF and export to XLS. Would prefer web interface, but can consider desktop as well. Any immediate recommendations?
Thanks.
Another marketing campaign :eek:
View 1 Replies View RelatedHi,I was wondering if someone could help advise me.Basically i have a client who wants to upgrade from a desktop msaccess solution to a web based system using MS SQL Server 2005.Basically their main concern is in losing the ability to write ad-hocqueries using the MS Access query manager. I can obviously writestatic reports etc but whats the best way to give them someflexibility ??Can anyone advise me if there is any product that can do this for SQLServer 2005 (which im sure there is) but will work in a webenvironment.Or does anyone have any helpful ideas ???all help / advice greatly appreciatedCG
View 1 Replies View RelatedI'm running the following SQL query from LabVIEW, a graphical programming language, using the built in capabilities it has for database connectivity:
DECLARE @currentID int
SET @currentID = (SELECT MIN(ExperimentID) FROM Jobs_t WHERE JobStatus = 'ToRun');
UPDATE [dbo].[Jobs_t]
SET [JobStatus] = 'Pending'
WHERE ExperimentID = @currentID;
SELECT @currentID AS result
<main.img>
This is the analogous code to main() is a C-like language. The first block, which has the "Connection Information" wire going into it, opens a .udl file and creates an ADO.NET _Connection reference, which is later used to invoke methods for the query.
<execute query.img>
This is the inside of the second block, the one with "EXE" and the pink wire going into it. The boxes with the gray border operate much like "switch" statements. The wire going into the "?" terminal on these boxes determines which case gets executed. The yellow boxes with white rectangels dropping down are invoke nodes and property nodes; they accept a reference to an object and allow you to invoke methods and read/write properties of that object. You can see the _Recordset object here as well. <fetch recordset.img>
Here's the next block to be executed, the one whose icon reads "FETCH ALL". We see that the first thing to execute on the far left grabs some properties of the recordset, and returns them in a "struct" (the pink wire that goes into the box that reads "state"). This is where the code fails. The recordset opened in the previous VI (virtual instrument) has a status of "closed", and the purple variant (seen under "Read all the data available") comes back empty.
The rest of the code is fairly irrelevant, as it's just converting the received variant into usable data, and freeing the recordset reference opened previously. My question is, why would the status from the query of the recordset be "closed"? I realize that recordsets are "closed" when the query returns no rows, but executing that query in SSMS returns good data. Also, executing the LabVIEW code does the UPDATE in the query, so I know that's not broken either.
Hi guys,
I am using DLinq with SQL Server Express 2005 and I need to install some kind of user interface which shows me a trace of the SQL statements sent. I would like to see them in real time (I won't like to inspect a log after :-).
I remember that there was something similar in SQL Server 2000 full edition, but I requiere the same functionality in the Express version now.
Could you tell me if there is some tool to do that?
Thanks in advance,
Erich.
Hi,
SQL Server 2005 SP1. (+ 5 minutes fix installed).
Query notification subscription cretaed (sys.dm_qn_subscriptions).
Subscription does not fire in response to event.
sys.transmission_queue is empty.
profiler does not show errors.
sys.conversation_endpoints shows one endpoint in status SO. (along with a lot of left over garbage - about 300K records - but this should not be a problem other than storage).
I know SP2 is suppose to solve the garbage records problem but this is not our problem. This setup was working and then just stoped.
any ideas ?
Boaz