OK, I have figured out how to hide the sys views and Information_Schema views from users but before I try it on the live database I have a question:
If I Deny Select on the Master Public Role for the sys views and Information_Schema views, what impact will this have for users other than not being able to see those views? Anyone know this?
Your feedback is greatly appreciated.
Thanks.
I want to determine the performance impact caused by the extensive use of the 'select into #' statement in a production environment. The current situation is that our reports team extensively uses the 'select into #' statement to build smaller subsets of data. These subsets are then used as the basis to create summary style reports and exports. All this is accomplished via the use of SQL pass-through.
After these reports/exports are completed and tested, they are then released to our operations department and the users. The reports/exports then can be run against the production server at the discretion of the user, provided they have the appropriate permissions. These reports/exports target the live data on the primary production server that already has been designated for the use of the application software.
Now I know that reporting against a transactional-based server, where the users run the application, is not a very good idea. (Inherited) I am currently migrating all reports/exports to a reporting server. Although it will still be transaction-based, the reports/exports will be isolated from user activity. Eventually we will be moving toward a warehouse scenario.
I also know that the extensive use of the 'select into #' statement is not a coding practice for use in production. I provided several alternatives to this practice
1) insert..select 2) insert..execute - from stored procedure
I have read that in the in sql 6.5 that this may cause severe performance and locking behaviors in system db's and tempdb. However, in the following document on the Microsoft Knowledge Base, it indicates that SQL 7.0 may have corrected this issue.
Q153441 - FIX SELECT INTO Locking Behavior.htm
Despite the indication of being corrected, I am still not convinced. I am frequently seeing drastic performance hits, especially when several of the reports are running. (which is very common) My concern is that moving these reports/exports to a reporting server may save the users; I believe that it may be migrating the problem to another location. I will be working with the developers to optimize their code and will investigate index issues.
** To make a long story short. I would like someone who has experience with this provide me with the top 5+ reasons not to use the 'select into #' methodology in a production environment. Further, if anyone has any documentation, I would surely like the info.
Thanks, Dave
Hello, everyone:
I want to add a column, INT NOT NULL DEFAULT 0, to a table. There are about 9 mil. records, 57 columns in the table. SQL 2k on Win 2003. What impact maybe bring?
1) Is there a down time on database and server?
2) Is it possible to insert records during adding new column?
3) How long will be taken roughly?
Thanks a lot.
ZYT
Hi All,
I want to know what will be the impact of changing the primarykey on a table which already has a lot of data.
For example, column A is unique, primary key. I want to make column B as unique, primary key.
Can I do that? What will be the impact on database performance?
Thanks
Sri
Hi,
Anybody have any idea howmuch % of performance will be affect if we are using varchar instead of char data type?.
Thanks,
Ravi
Hi,
I am looking for a tool that is similar to SQL Impact (Quest). Quest has discontinued the tool.
This tool should be able to detect all database object dependencies for SQL Server, Sybase and Oracle. The objects should include tables, views, stored procedures, indexes and other objects. This should also detect DB object dependencies in front end applications as well.
Any suggestions are greatly appreciated...
Thanks!
I have been collecting information about 20 performance counters (memory, IO, cpu, SQL) that refresh every 15 seconds, would that have any performance hit in the server? what are best practices when collecting information via performance counters?
Thanks
I want to use "on delete cascade" in one of my tables but I'm worried though whether this can affect the performance when having millions of records. To explain more I'm working on a social networking website and I have two tables UserAccounts, in which I only keep the username and password and a few related fields, and Profiles in which I keep the profile data for users, I want to be sure that I won't have any records in the Profiles table without corresponding records in the UserAccounts table. Please see the DDL below to understand more the structure of the tables:
CREATE TABLE UserAccounts
(
UserID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
UserName VARCHAR(20) NOT NULL,
Password VARCHAR(20) NOT NULL,
--other fields (e.g. last login .. etc)
)
CREATE TABLE Profiles
(
UserID INT NOT NULL REFERENCES UserAccount(UserID),
-- other fields (e.g. birthdate, nationality .. etc)
)
Any suggestions are highly appreciated...
Hi,
Does anyone know how the key influencers impact values are calculated? Thanks!!
Kate
Hi,
I am currently working on with the ASP encryption of my application. I've tried to test the encryption of the connection string using the capicom.dll in my local, and it works successfully. However, I am not quite sure if this will still work after my OS is upgraded to WIN2K3 (my current OS is WINXP). Will this dll component be impacted after the OS Upgrade? or will there be no impact at all?
Any inputs from you guys would be much appreciated.
Thank you.
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies View RelatedI am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies View RelatedThanks to all participants.
I am using SQL Server 2000 with replication object for two location. Log size on publisher go upto 25 times of data file size, I mean 80 MB Data files has maintains 2 GB log file and it is same for all five co's working on same windows 2000 advanced server board.
Since last week server randamly get disconnected from user applications and at that time few tables are not openable at server.
Can any one give a reason ? Why this type misbehaviou done by SQL Server 2000?
Thanks.
I have a question regarding FUll and differential backup.
We we take full or diff back up, does it create lot of logs ie. Does full or diff backup has any impact on log size?
Thanks
We have the following scenario:
Server A replicates Database A to Server B.
Server C has Database A on it as well, but in standby mode. We are applying the transaction logs generated by Database A on Server A to the database on Server C leaving it in standby mode each time.
Let's say we had planned maintenance for Server Aand dumped the last set of transactions on Server A in standby mode to be applied to to Server C. What happens to the replica on Server B? When I start to use Server C, can I backup its transactions and apply them to Server A, and then have those transactions replicated to Server B? And then what do I do when the maintenance is complete so that I can swithc back to Server A and have the replication continue on as before the maintenance to Server B?
Thanls
What would happen to the the queries which are under execution when I change the MAXDOP value from say 0 to 1?
View 11 Replies View RelatedJust a quick easy question. If I alter a table (add a column to the table), will it take the table offline during the ALTER process? I am adding the column to the end of the table not in the middle. I know if I add it in the middle it will offline the table.
View 4 Replies View Relatedhi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
I have a sp. I alter stored procedure by adding some logic to that. How can I test that is there any functional impact by altering that stored procedure? How to prove to the team that the modifications doesn't impact any functionality?
View 5 Replies View RelatedOur SQL Server 2008 r2 has collation Latin general.
And my database 'DB1' , 'DB2' has collation set to Japanese unicode.
My sql team has informed they cannot change system collation as it hosts other db's as well.
My Query: What will be the impact of changing collation from JP to Latin.
We are using SSIS for the first time. My team is working on a project that involves putting a date time stamp into a series of tables. These tables are being assembled in a series of child packages being executed by the parent. When the parent runs, we evaluate our timestamp variable as a GETDATE() expression and pass it to the children to be included as a derived column. We don't want the actual runtime of each step to be the timestamp, just the start of the batch (parent).
In order to get the variable to pass over to the child, we needed to set the package location to "file system"instead of "SQL Server". It seems unusual that this would be so. Are we doing something wrong?
What implications does this have for deployment? Will we need to customize the packages for each instance we plan to run this on? Can you have a parent run a child package on a different instance? This would be a performance plus since we have really huge source databases and would like to distribute the processing.
Hmmm, my boss just told me to scratch the whole idea of parent-child and go with a control table to store the variable for all the packages to access. Oh well, I'm still interested in why this is so cumbersome when really its just passing a parameter from one procedure to another.
Oh, and I think you could use a spellchecker on this message box. At least I could use one.
Hello All,
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
Thanks for your help!
System Configuration :
OS : Windows 2003 latest SP
SQL Server : Standard Edition, SP2
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
DownTime : This is not a 24x7 kind of machine. It can have downtime
Reference : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1198044&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1
I have been discussing this Service Broker issues in this forum for quite sometimes and sorry to bother u all again€¦ To make things more clearer before implementing in production environment I have few doubts and it should be clarified..
As discussed in the first link we can clear sys.conversation_endpoints by just giving ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK IMMEDIATE. But my apprehension is that if we run this command on production server and it truncate this table€¦ what will be the impact and overall overhead on the system€¦ Is it recommended to give this statement on Production Server daily at less traffic times, to clear this table ?€¦ will it have adverse effect. I repeat I have downtime , I can even shutdown this server daily€¦
I also, just want to know why Microsoft has not looked into this aspects€¦ why the system itself is clearing the expired messages.. What is the thought behind this architecture
I have the script to run in batch€¦ but in high-level meetings it is always difficult to convince this architecture/process€¦
Thanks in advance
I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.
I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?
I am using SQL 2005 build 9.0.2227
I have a custom conflict resolver - which fires on update conflicts (using row level tracking)
I have had a couple of occasions when the resolver has failed with the following error:
"The schema of the custom Dataset object implemented in the business logic handler does not match the schema of the source Dataset object. Verify that the custom Dataset object has been correctly defined"
In both cases I found that the row for which a conflict was being handled was not a conflict at all. One was a straightforward non conflicting update at the publisher and the other was a similar update at the subscriber.
I got round the problem by temporarily using a fix version of the conflict resolver dll that either set the custom Dataset to the publisher dataset or the subscriber dataset - depending on where the update had occurred.
When the first error (publisher update) occurred - the resolver code was basing the custom dataset on the publisher dataset - which was presumably empty - so I changed the code to base the custom dataset on the subscriber dataset. The second error therefore occurred when the custom dataset was based on the subscriber dataset - which again was presumably empty
Note that the tables involved in each occasion were different and neither table is filtered.
Is there a known bug in this area?
I am considering trying to change the resolver code to identify false conflicts in order to workround the problem - but this would be difficult to test as I can't reproduce the problem
aero1
SQL Server 2000:
Question 1
If you drop / rename a table and then recreate the table with the SAME NAME, what impact does it have on stored procedures that use these tables? From a system perspective, do you have to rebuild / recompile ALL the stored procedures that use this table?
I had a discussion with someone that said that this is a good idea, since the IDs of the tables change in sysobjects and from a SQL SERVER query plan perspective, this needs to be done...
Question 2
If you Truncate a Table as part of a BEGIN TRANSACTION, what happens if an error occurs? Will it Rollback? The theory is that it won't because Truncate doesn't utilize the logs where as Delete From uses the SQL Logs?
Thanks!
What causes SQL Server 2000 to create tables with default column sizes of 8000 for a varchar datatype??
I would assume this can cause significant performance problems.
(see attached)
I would like to know the impacts (if any) of adding nonclustered index with included columns on large tables (these tables are populated by bulk insert from text files).
View 3 Replies View RelatedWhat is the impact on the users to drop an index on a table while in use? I will recreate the index afterwards. The table is used constantly by a three of processes/users at all times.
View 3 Replies View RelatedFor example in a Select Statement we have many tables and we have Where Clause with many conditions with AND operations. Do the SQL SERVER would apply the Where clause after all fetch or can dynamically decide about to include the related Tables from Select Statement Orderly with respect to where clause predicates? (SQL SERVER would not fetch data of those tables for its Select, where the AND condition in Where clause fails or by logic would be fruitless/not-related.)
View 5 Replies View Related
If I have 6-8 queries running in parallel, Whether having a Single connection Manager (for the same source) for all the Extract performs faster or having Distinct Connection Manager for each of the extract performs faster ?
Regards
Subhash Subramanyam
Hi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh